info
Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
A macro that deletes each case where cells in column C are = 0
103 views
Skip to first unread message
AlanN
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?
my cursor is in that has the value '0' or is blank?
TIA, AlanN
Myrna Larson
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.
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
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
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.
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
Feb 17, 2003, 1:18:27 PM2/17/03
to
AIR, it's a trick I picked up here, from somebody else <g>.
0 new messages