DanOfEarth
06-13-2010, 02:11 PM
Help please,
This is supposed to delete "address rows" on worksheet "Leads" based on a list of zip codes in worksheet "Info"
I'm having trouble on my actual delete line (the red one). Not sure how to reword it:
Sub DelRows()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CriteriaRng As Range
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With Sheets("Info")
Set CriteriaRng = .Range("B25", .Cells(Rows.Count, "B").End(xlUp))
End With
'Loop through the cells in the Criteria range
For Each Cell In CriteriaRng
With Sheets("Leads")
'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
'Loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1
'Check the values in the B column
With .Cells(Lrow, "B")
If Not IsError(.Value) Then
If .Value = Range("CriteriaRng") Then .EntireRow.Delete
End If
End With
Next Lrow
End With
Next Cell
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
Anybody know why.
thank you very much.
This is supposed to delete "address rows" on worksheet "Leads" based on a list of zip codes in worksheet "Info"
I'm having trouble on my actual delete line (the red one). Not sure how to reword it:
Sub DelRows()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CriteriaRng As Range
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With Sheets("Info")
Set CriteriaRng = .Range("B25", .Cells(Rows.Count, "B").End(xlUp))
End With
'Loop through the cells in the Criteria range
For Each Cell In CriteriaRng
With Sheets("Leads")
'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
'Loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1
'Check the values in the B column
With .Cells(Lrow, "B")
If Not IsError(.Value) Then
If .Value = Range("CriteriaRng") Then .EntireRow.Delete
End If
End With
Next Lrow
End With
Next Cell
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
Anybody know why.
thank you very much.