Conditional row deletion using range comparison?

jmckeone

Well-known Member
Joined
Jun 3, 2006
Messages
550
I'm trying to determine how to use VBA to do a conditional row deletion. The sketch below gives you and idea what I want to accomplish. I want to compare the contents of column A on the first sheet to column A on the second sheet and delete rows on the first tab which don't appear on the keep list. Thoughts?

LocCompare.jpg
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi jmckeone

please try:

Code:
Sub DelLoc()
Dim lRow As Long, rKeep As Range

Set rKeep = Worksheets("Keep").Range("A2", Worksheets("Keep").Range("A2").End(xlDown))
With Worksheets("Loc")
    For lRow = .Range("A2").End(xlDown).Row To 2 Step -1
        If rKeep.Find(.Range("A" & lRow), LookIn:=xlWhole) Is Nothing Then .Rows(lRow).Delete
    Next
End With
End Sub

Hope this helps
PGC
 

jmckeone

Well-known Member
Joined
Jun 3, 2006
Messages
550
tried running it and it fails ... when I run debug the section highlighted is

Code:
If rKeep.Find(.Range("A" & lRow), LookIn:=xlWhole) Is Nothing Then
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

Hi again

I tried but I could not reproduce your error.

I used eactly the layout you specified. A worksheet named Loc, one named Keep and the values you posted.

It worked just fine.

What you can do is in the line with the error check

- rKeep.address
- lRow

Maybe it will help understanding why the error.

Hope you find the bug
PGC
Book1.xls
ABCD
1Location
2A
3B
4C
5D
6E
7F
8G
9
10
11
Loc
Book1.xls
ABCD
1Keep
2A
3C
4E
5
6
7
8
9
10
Keep
 

jmckeone

Well-known Member
Joined
Jun 3, 2006
Messages
550
copied code and mirrored tab names precisely as shown and yet obtain the following error

failure.jpg
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi jmckeone

I'm glad that now it works!!!

Cheers
PGC

P. S.

This was the final version, without the error in the Find method:

Code:
Sub DelLoc()
Dim lRow As Long, rKeep As Range
Dim rRng As Range

Set rKeep = Worksheets("Keep").Range("A2", Worksheets("Keep").Range("A2").End(xlDown))
With Worksheets("Loc")
    For lRow = .Range("A2").End(xlDown).Row To 2 Step -1
        If rKeep.Find(.Range("A" & lRow), LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then .Rows(lRow).Delete
    Next
End With

End Sub
 

jmckeone

Well-known Member
Joined
Jun 3, 2006
Messages
550
Ran into some issues trying to make real world application but fortunately with a bit of assistance from my brother who works as a coder I was able to revise the code to that shown below

Code:
Sub DelLoc()

Dim lRow As Long, rKeep As Range
Dim rRng As Range
Dim EndRow As Integer

Set rKeep = Worksheets("Keep").Range("A2", Worksheets("Keep").Range("A2").End(xlDown))

EndRow = ActiveSheet.UsedRange.Rows.Count

With Worksheets("Loc")
    For lRow = EndRow To 2 Step -1
        If .Range("A" & lRow) <> "" Then
            If rKeep.Find(.Range("A" & lRow), LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
            .Rows(lRow).Delete
        End If
            End If
    Next
End With

End Sub
 

Forum statistics

Threads
1,141,311
Messages
5,705,680
Members
421,405
Latest member
buster015

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top