MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VBA beginner has a problem

Posted by Doug Potter on October 19, 2001 4:29 AM

I am a vba rookie. This is my first procedure. I am attempting to delete rows(starting at row 2) based on the contents of the first cell in the row. I want to delete the row if the first cell contains "Sls" or contains "---". It works if the cell value matches "Sls" or "---" exactly but I can't get it to delete the row if the cell contains other characters such as
"aSls" or "------". The procedure doesn't bomb, it just doesn't perferm any row deletions. I have been attempting to use LIKE but it still doesn't work. Any suggestions. I have written this to work from the bottom up.



Sub DeleteRows()

l = ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
Range(Cells(2, 1), Cells(l, 1)).Select
For r = l To 2 Step -1
If Cells(r, 1).Value = "sls" Then _
Rows(r).Delete Shift:=xlUp
Next r

End Sub

Posted by Mark O'Brien on October 19, 2001 5:21 AM


One to do this is to use the "Instr" command.
It basically just checks to see if a string exists within another string. It then returns the position of the string if it's found. (not a great explanation, but the Excel help will explain it better. Anyway, here's how you could use it in your "If...Then" statement

If InStr(1, Cells(1, 1).Value, "sls") Then _
Rows(r).Delete Shift:=xlUp