Deleting multiple rows contain 0 in excel 2000

[Guest]

I have a rather large spreadsheet and need to delete multiple rows with
contain a value of 0. I ran a macro which worked, but it deleted not only
rows which contained 0, but all other rows which contained figures has a 0.
How can I delete rows containing ONLY 0 and not rows containing figures like
650? Or is there another method I should use for this, or is it possible to
do this without having to delete these rows one by one. The following is
what I used as a macro:

Sub Find_0()
Dim rng As Range
Dim what As String
what = "0"
Do
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
Else
Rows(rng.Row).Delete
End If
Loop
End Sub

Thanks so much!

 

[Chip]

Check this out...http://www.mvps.org/dmcritchie/excel/delempty.htm

Read the part about delete all entries with N marked rows...rather than
find "0" it goes through the cells and checks to see if the cell equals
the value you set which will work better for what you want

 

[Guest]

thanx chip for your quick response. Found the macro you specified, but where
to I put the value 0? I can't get it to work.

 

[Chip]

It should read:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim lastrow As Long, r As Long
lastrow = ActiveSheet.UsedRange.Rows.Count
For r = lastrow To 1 Step -1

SEE NEXT LINE=>
If UCase(Cells(r, 31).Value) = 0 Then Rows(r).Delete
Next r
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating=True

You can remove the quotes around "N" since you arent looking for text,
you are looking for a number.

 

[Guest]

Well, unfortunately that didn't work. The only thing I did was replace the
"N" with 0 and nothing changed. Thanks for your time anyway.

 

[Chip]

I see where I made my mistake, how many columns of data do you have
(i.e. are the zeros you want it to look for all in the same column)?

 

[Chip]

My bad, try this:

Sub Delete_0_MarkedRows()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim lastrow As Long, r As Long
lastrow = ActiveSheet.UsedRange.Rows.Count
lastcolumn = ActiveSheet.UsedRange.Columns.Count
For c = lastcolumn To 1 Step -1
For r = lastrow To 1 Step -1
If (Cells(r, c).Value) = 0 Then Rows(r).Delete
Next r
Next c
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[Tom Ogilvy]

Sub Find_0()
Dim rng As Range
Dim what As Long
With ActiveSheet.UsedRange
what = 0
Set rng = .Find(what:=what, _
After:=Range("A1"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Do
Rows(rng.Row).Delete
Set rng = .FindNext(.Range("A1"))
Loop Until rng Is Nothing
End If
End With