David has attended:
Excel VBA Intro Intermediate course
Deleting rows with a O
Hi, I've been using the code below to remove rows based on specific value of the target column. It works fine until I try and use the code to remove the row's where the value is 0. Rather than picking up just those with a 0, it removes anything with a 0 in eg 50126. Please can you advise?
Private Sub DeleteRows(ColumnLetter As String, DataItem As String, RowNumber As Integer)
Dim c As Range
Dim SrchRng
Set SrchRng = ActiveSheet.Range(ColumnLetter & "1", ColumnLetter & RowNumber)
Do
Set c = SrchRng.Find(DataItem, LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing
End Sub
RE: Deleting rows with a O
Hi David
Try editing the line which carries out the search to:
Set c = SrchRng.Find(DataItem, LookIn:=xlValues, LookAt:=xlWhole)
(in one line)
Your code will then only find and delete rows where the whole cell is 0.
If instead you put
LookAt:=xlPart
then any value containing a 0 will be found and deleted.
If the LookAt is missed out Excel chooses the current value in 'Match entire contents' option in the Find dialog box.
Please let me know if the suggestion works, thanks.
Doug Dunn
Best STL
