code to delete rows

[Guest]

this is probably very simple but i am new to writing code (excel 2000).

i want to scan cells M9:M999, and for every cell where the value is 0 i want
that row deleted. so for instance M9's value is 453 and row 9 will stay, but
M10's value is 0 so row 10 is deleted. so on and so forth.

thanks so much!
ianalexh

 

[Bob Phillips]

Do a filter (Data>Filter>Autofilter) and select a value of 0. Delete the
visible rows.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ron de Bruin]

Try this example

Sub Example2()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 9
EndRow = 999
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "M").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "M").Value = 0 Then .Rows(Lrow).Delete
'This will delete each row with the Value 0 in Column M.

End If
Next
End With

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

 

[Guest]

i'm sorry; i wasn't specific. i want to set a macro to accomplish this, so
not-so-savvy computer users can click a button and the job gets done.

ianalexh

 

[Bob Phillips]

Follow the steps I gave and record a macro while doing it.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Not quite as easy as I suggested, so here is some code

Sub DeleteZeroes()
Dim rng As Range
Set rng = Range("M8:M999")
rng.Cells(1, 1).EntireRow.Insert
rng.AutoFilter Field:=1, Criteria1:="=0"
rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)