Quick ? on deleting rows

[DavidC]

I've got the delete rows thing working, deleting rows if a cell contains certain data. Now, how do I delete the row if the cell DOES NOT contain certain data? Thanks.

 

[Colo]

Hi! You mean like this?
I am diffident in whether the code which I presented is filling your demand.
Regards,

Sub test()
Dim i As Long, rngDel As Range
For i = Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(i)) = 0 Then
If rngDel Is Nothing Then
Set rngDel = Rows(i)
Else
Set rngDel = Union(rngDel, Rows(i))
End If
End If
Next
rngDel.EntireRow.Delete
End Sub

 

[Dave Hawley]

Hi Dave

I have many eaxmples of deleting rows to be found here. Most avoid the use of Loops as loops slow things down.
http://www.ozgrid.com/VBA/VBACode.htm

I also have a very quick method here:
http://www.ozgrid.com/VBA/VBALoops.htm
That does use a loop, but is sped up dramitically by dealing only with the cells needed.