Move/Delete Entire Row If Value of Cell Matches a Range From Another sheet

[kimpoy1209]

Hi Excel masters.

I am working on one excel report which is to be used repeatedly.
However, I am stuck with this one challenge right now. Please help!

Here is what I am trying to achieve.

1. Move an entire row to another sheet if a value of it's column is found in a range that is located in another worksheet.
e.g. Sheet 1, column C1 is the data I need to check.
Sheet 2, Range B1:B20 is the range needs to be checked.
If the value in C1 is found in Sheet 2, B1:B20, then the row should be moved to a new worksheet.

Thank you in advance!

 

[LloydFinancials]

Sub findnmove()



Dim rgFound As Range



'Sheet1!C1 has the lookup value.
'Sheet2!A1:A5 is the table array.
'Sheet3!A1 is the destination cell where the whole row gets pasted
'Found row in Sheet2 is then deleted.


Set rgFound = Sheets("Sheet2").Range("A1:A5").Find(Sheets("Sheet1").Range("C1"))
Sheets("Sheet2").Cells(rgFound.Row, 1).EntireRow.Copy Sheets("Sheet3").Range("A1")
Sheets("Sheet2").Cells(rgFound.Row, 1).EntireRow.Delete



End Sub

 

[kimpoy1209]

Hi Lloyd! Thank you!

Is it also possible to help me with the complete code? since I'm dealing with thousands of rows.
What's needed is to make sure that the row that will be deleted gets pasted to the next available row in the destination column.
I need to look into thousand columns, so there is a routine/loop of lookups

Thank you!

 

[JLGWhiz]

Re: Macro To Move Row to New Sheet if Cell Value is Found on A Range (A3:A40)

You dd not specify which column to search in the Master data sheet, so I used A:A. If that is incorrect you can change it to the correct column in the code below.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, c As Range, fn As Range
Set sh1 = Sheets("Master data")
Set sh2 = Sheets("Lookup Table")
Set sh3 = Sheets("New Table")
    For Each c In sh2.Range("A2", sh2.Cells(Rows.Count, 1).End(xlUp))
        Set fn = sh1.Range("A:A").Find(c.Value, , xlValues) 'If not column A, change to correct column
            If Not fn Is Nothing Then
                fn.EntireRow.Copy sh3.Cells(Rows.Count, 1).End(xlUp)(2)
            End If
    Next
End Sub

EDIT
Moved from the duplicate thread

 

[Fluff]

Cross posted https://www.ozgrid.com/forum/forum/...-sheet-if-it-s-cell-value-is-found-on-a-range

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[kimpoy1209]

Hi Fluff,

My apologies. Yes. I have closed the other one.
Again, sorry. Thank you and I do appreciate all the help here

 

[kimpoy1209]

Re: Macro To Move Row to New Sheet if Cell Value is Found on A Range (A3:A40)

This does not seem to work for my case. Nothing is happening.

 

[Fluff]

Re: Macro To Move Row to New Sheet if Cell Value is Found on A Range (A3:A40)

This does not seem to work for my case. Nothing is happening.

Incase you haven't noticed I moved the code by JLGWhiz, from the other thread, where your locations were different.

 

[kimpoy1209]

Re: Macro To Move Row to New Sheet if Cell Value is Found on A Range (A3:A40)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Age
[/TD]
[TD]Nick
[/TD]
[TD] Fave Food
[/TD]
[TD]BType
[/TD]
[TD]Code
[/TD]
[/TR]
[TR]
[TD]Rico
[/TD]
[TD]32
[/TD]
[TD]Ric
[/TD]
[TD]Slurpy
[/TD]
[TD]A
[/TD]
[TD]123
[/TD]
[/TR]
[TR]
[TD]Brock
[/TD]
[TD]30
[/TD]
[TD]Brock
[/TD]
[TD]Drumstick
[/TD]
[TD]B
[/TD]
[TD]124
[/TD]
[/TR]
[TR]
[TD]Roman
[/TD]
[TD]28
[/TD]
[TD]Rome[/TD]
[TD]Cake
[/TD]
[TD]O
[/TD]
[TD]135
[/TD]
[/TR]
[TR]
[TD]Dolph
[/TD]
[TD]30
[/TD]
[TD]Zig
[/TD]
[TD]Pizza
[/TD]
[TD]B
[/TD]
[TD]122
[/TD]
[/TR]
[TR]
[TD]Kurt
[/TD]
[TD]32
[/TD]
[TD]Angle
[/TD]
[TD]Twix
[/TD]
[TD]A
[/TD]
[TD]241
[/TD]
[/TR]
</tbody>[/TABLE]

Lookup Range(Sheet2)
[TABLE="width: 500"]
<tbody>[TR]
[TD]123
[/TD]
[/TR]
[TR]
[TD]124
[/TD]
[/TR]
[TR]
[TD]135
[/TD]
[/TR]
[TR]
[TD]122
[/TD]
[/TR]
[TR]
[TD]201
[/TD]
[/TR]
</tbody>[/TABLE]

Here's my challenge.
1. If the column F(Code) is found within the lookup range (Sheet2), then it's entry in the master table should be moved to another sheet(e.g. sheet3). This goes on for the rest of the values, depending on the code( all 123 goes to table3, all 124 goes to table 4 so on so forth...)

2. The destination row where the cut rows would be pasted should be the next available row so that it would not overwrite what is already there.

Thank you in advance for the help!
Thank you thank you thank you!

 

[kimpoy1209]

Hi Excel masters.

I am working on one excel report which is to be used repeatedly.
However, I am stuck with this one challenge right now. Please help!

Here is what I am trying to achieve.

1. Move an entire row to another sheet if a value of it's column is found in a range that is located in another worksheet.
e.g. Sheet 1, column C1 is the data I need to check.
Sheet 2, Range B1:B20 is the range needs to be checked.
If the value in C1 is found in Sheet 2, B1:B20, then the row should be moved to a new worksheet.

Thank you in advance!

Adding cross post link: https://www.ozgrid.com/forum/forum/...-sheet-if-it-s-cell-value-is-found-on-a-range