Delete Rows in Worksheet2 that Match Criteria in Worksheet1!A1:A20

[SteveC]

I have a macro put together by Ron de Bruin a fewmonths ago... wondering how
I can modify it so that it will delete rows in worksheet2 if ColB in
Worksheet2 contain values referenced in Worksheet1!A1:A200.

In otherwords, how can I modify the line DeleteValue = "*Total*" to
make this work?

thanks very much!



Sub Delete_Row_Multiple_Criteria_with_Autofilter()
Dim DeleteValue As String
Dim rng As Range

DeleteValue = "*Total*"
With ActiveSheet
.Range("A1:A1000").AutoFilter Field:=1, Criteria1:=DeleteValue
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete

End With
.AutoFilterMode = False
End With
End Sub

 

[Ron de Bruin]

Hi Steve

You can loop through the column in Worksheet2 and test it like this

with worksheet2 active and the list in Sheets("Sheet1").Range("A1:A200") try this

Sub Example3()
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim StartRow As Long
Dim EndRow As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For Lrow = EndRow To StartRow Step -1

If IsError(.Cells(Lrow, "B").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf Not IsError(Application.Match(.Cells(Lrow, "B").Value, _
Sheets("Sheet1").Range("A1:A200"), 0)) Then .Rows(Lrow).Delete

End If
Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

 

[SteveC]

run, thanks for posting this... a big help... Stevec

 

[SteveC]

Ron, I thought this same macro could be used to copy formatting from a
matching cell, and to apply that formatting to a row as, instead of deleting
a row.. if you have an interest, could you take a look at this: thanks for
your help...

http://www.microsoft.com/office/com...&p=1&mid=17c2da3a-a4db-472e-8ea5-5909440027f4