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

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

"andresg1975" wrote in message
...
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

it didn't work compile error


"Bob Umlas" wrote:

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

"andresg1975" wrote in message
...
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

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

--
Regards,
Tom Ogilvy


"andresg1975" wrote:

it didn't work compile error


"Bob Umlas" wrote:

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

"andresg1975" wrote in message
...
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

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

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

Dave Parker

andresg1975 wrote:
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

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

Dave

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

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

Dave Parker

andresg1975 wrote:
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

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.


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"andresg1975" wrote:

it didn't work compile error


"Bob Umlas" wrote:

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

"andresg1975" wrote in message
...
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