PDA

View Full Version : Sleeper: Customize KB Code to Delete Rows



Anne Troy
06-27-2005, 09:40 PM
I want to delete rows according to ALL values in Column A of Sheet2 (there's about a dozen).

The column to look in is on sheet1, Column L.

So, if a specific word is found (yes, that's the only word in the cell) in Column L of Sheet1, the entire row it's found in is deleted. :)

Dave's got some code here; dunno if it can be altered.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=260

Bob Phillips
06-28-2005, 02:28 AM
I want to delete rows according to ALL values in Column A of Sheet2 (there's about a dozen).

The column to look in is on sheet1, Column L.

So, if a specific word is found (yes, that's the only word in the cell) in Column L of Sheet1, the entire row it's found in is deleted. :)

Something like this?



Sub test()
Dim iLastRow As Long
Dim i As Long
Dim colWords As Collection
Application.ScreenUpdating = False
Set colWords = New Collection
'build a collection of values to compare against
'from the list in column A of Sheet2
With Worksheets("Sheet2")
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value <> "" Then
colWords.Add i, .Cells(i, "A").Value
End If
Next i
End With
'loop through column L of Sheet1 and check if value is in
'the collection, if so delete it
With Worksheets("Sheet1")
iLastRow = .Cells(Rows.Count, "L").End(xlUp).Row
For i = iLastRow To 1 Step -1
If ExistsInCollection(colWords, .Cells(i, "L").Value) Then
.Rows(i).Delete
End If
Next i
End With
Application.ScreenUpdating = True
End Sub

Public Function ExistsInCollection(col As Collection, ByVal sKey As String)
On Error GoTo NoSuchKey
If VarType(col.Item(sKey)) = vbObject Then
' force an error condition if key does not exist
End If
ExistsInCollection = True
Exit Function
NoSuchKey:
ExistsInCollection = False
End Function

mdmackillop
07-11-2005, 10:29 AM
Hi DB,
Is this solved?