A macro that deletes each case where cells in column C are = 0

103 views
Skip to first unread message

AlanN

unread,
Feb 16, 2003, 5:14:38 PM2/16/03
to
Can anyone offer me a simple macro that deletes all rows in the column that
my cursor is in that has the value '0' or is blank?


TIA, AlanN

Myrna Larson

unread,
Feb 16, 2003, 5:53:57 PM2/16/03
to
First I clear the cells containing 0. Then I use one of XL's special selection commands to
select the blank cells. Then I delete those rows.

Sub DeleteRows()
Dim Rng1 As Range
Dim Rng2 As Range

Set Rng1 = Intersect(Selection.Cells(1).EntireColumn, Selection.Parent.UsedRange)
Rng1.Replace 0, "", xlWhole

On Error Resume Next
Set Rng2 = Rng1.SpecialCells(xlCellTypeBlanks)
If Rng2 Is Nothing Then
'nothing to delete
Else
Rng2.EntireRow.Delete
End If

End Sub

AlanN

unread,
Feb 16, 2003, 6:04:31 PM2/16/03
to
This works like a charm! Thanks for taking the time.

AlanN


"Myrna Larson" <myrna...@charter.net> wrote in message
news:gd505v86bcmpln2g2...@4ax.com...

David McRitchie

unread,
Feb 16, 2003, 9:35:06 PM2/16/03
to
Nice one Myrna, never thought of replacing 0 as entire value so that
they can be treated same as empty values and removed
all at once without a loop.

"Myrna Larson" <myrna...@charter.net> wrote ...


> First I clear the cells containing 0. Then I use one of XL's special selection
> commands to select the blank cells. Then I delete those rows.

> [clipped out sample macro]


Myrna Larson

unread,
Feb 17, 2003, 1:18:27 PM2/17/03
to
AIR, it's a trick I picked up here, from somebody else <g>.
Reply all
Reply to author
Forward
0 new messages