Thread
:
Delete rows if value matches
View Single Post
#
2
Posted to microsoft.public.excel.programming
Don Guillett
external usenet poster
Posts: 10,124
Delete rows if value matches
Maybe you have a case problem. Try looking for any spelling Dave, DAVE,
dave,dAvE
If ucase(.Cells(Lrow, "A"))= "RON" And _
ucase(.Cells(Lrow, "B"))= "DAVE" And _
.Cells(Lrow, "C").Value 10 Then .Rows
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Rob" wrote in message
...
Having some success but stuck with checking values in several columns.
The below was taken from Ron de Bruin's web site (which is great I must
add) but I can't get the code to delete rows where the criteria matches.
Also, once sorted, is there an option to do the same but us OR instead of
AND eg. if contents of cell column A = ron, delete or if contents of cell
in column B = dave, delete and so on.
Thanks, Rob
http://www.rondebruin.nl/delete.htm#Loop
'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1
'We check the values in the A column in this example
With .Cells(Lrow, "A")
If Not IsError(.Value) Then
If .Cells(Lrow, "A").Value = "ron" And _
.Cells(Lrow, "B").Value = "dave" And _
.Cells(Lrow, "C").Value 10 Then .Rows(Lrow).Delete
'If .Value = "ron" Then .EntireRow.Delete
'This will delete each row with the Value "ron"
'in Column A, case sensitive.
End If
End With
Next Lrow
Reply With Quote
Don Guillett
View Public Profile
Find all posts by Don Guillett