Deleting multiple rows contain 0 in excel 2000
 

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!

Deleting multiple rows contain 0 in excel 2000
 

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

Deleting multiple rows contain 0 in excel 2000
 

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

Deleting multiple rows contain 0 in excel 2000
 

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.

Deleting multiple rows contain 0 in excel 2000
 

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!

Deleting multiple rows contain 0 in excel 2000
 

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

Deleting multiple rows contain 0 in excel 2000
 

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

Deleting multiple rows contain 0 in excel 2000
 

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.