create macro that erases rows having blank cells and

[Guest]

let's say i have this:

column O

row 1 750
row 2 0
row 3 blank cell
row 4 ----------
etc

how can i create a macro that looks column O, delete rows that contain 0
values, blank cells, and ---------.

thanks for your help

 

[Bob Umlas]

Try this:
Sub DeleteRows()
n=Activesheet.Usedrange.Rows.Count
For i=n to 1 step -1
if cells(i,15).value="----------" or cells(i,15).value=0 or
isblank(cells(i,15)) then rows(i).delete
Next
End Sub

Bob Umlas
Excel MVP

 

[Guest]

it didn't work compile error

Try this:
Sub DeleteRows()
n=Activesheet.Usedrange.Rows.Count
For i=n to 1 step -1
if cells(i,15).value="----------" or cells(i,15).value=0 or
isblank(cells(i,15)) then rows(i).delete
Next
End Sub

Bob Umlas
Excel MVP

 

[Guest]

here is a revision that changes Isblank to Isempty and uses itty bitty lines
so you don't have word wrap problems. compiled for me:

Sub DeleteRows()
n = ActiveSheet.UsedRange.Rows.Count
For i = n To 1 Step -1
If Cells(i, 15).Value = _
"----------" Or Cells(i, 15) _
.Value = 0 Or IsEmpty( _
Cells(i, 15)) Then _
Rows(i).Delete
Next
End Sub

 

[dgp]

I'm not sure how the perfomance compares to previous solutions but this
seems to work.

Sub DeleteColumnOBlankCells()
Columns("O:O").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End sub

Dave Parker

 

[dgp]

Sorry I didn't read closely enough - I see now that you also wanted to
delete zeroes.

Dave

 

[Guest]

I have used your "delete rows" code in my application and it works perfectly.
I just have a question - how does the macro finds the last member of the
list? I'm assuming it has something to do with the
ActiveSheet.UsedRange.Rows.Count statement. At first I thought "UsedRange"
must be a named range, but its not.