Quick ? on deleting rows

DavidC · Mar 25th, 2002, 09:25 AM

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 · Mar 26th, 2002, 10:44 PM

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 · Mar 26th, 2002, 11:03 PM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 25th, 2002, 09:25 AM	#1
DavidC New Member Join Date: Mar 2002 Posts: 26	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.

Mar 26th, 2002, 10:44 PM	#2
Colo MrExcel MVP Join Date: Mar 2002 Location: Kobe, Japan Posts: 1,420	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

Mar 26th, 2002, 11:03 PM	#3
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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.