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!

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

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

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

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.

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.

"Cathy" wrote:

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!

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)?

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

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

--
Regards,
Tom Ogilvy





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