Delete rows using VB

[Miguel]

Hi,
I'm using a macro that delete rows that are blank. However, I want the macro
to delete rows that are equal to zero. This is my program:

Sheets("Group-rep").Select
Range("B11:B142").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
Range("A1").Select

what should I put on the SpecialCells?
Is there a way in the help option to find out what I should use?

Thanks
Miguel

 

[Don Guillett]

How about a nice for each loop that does NOT use SELECT. Or use
data>filter>filter for 0>delete. Record it.

for each c in range("b11:b142")
if c=0 then rows(c.row).delete
next c

 

[Mike H]

Hi,

You can't easilly move down through a range conditionally deleting rows as
you go. If you do then if 2 consecutive rows meet the criteria you'll miss
the second one. You are better goung backwards through the range or use this
method.

Sub Marine()
Dim MyRange As Range
For Each c In Range("b11:b142")
If c.Value = "" Then 'or =0 if that's what you want
If MyRange Is Nothing Then
Set MyRange = c.EntireRow
Else
Set MyRange = Union(MyRange, c.EntireRow)
End If
End If
If Not MyRange Is Nothing Then
MyRange.delete
End If
Next
End Sub

Mike

 

[Ron de Bruin]

There is no option in SpecialCells for a zero

See this page for a few ways
http://www.rondebruin.nl/delete.htm

 

[Mike H]

Here's a method for working backwards that will only delete the row if the
value is 0 (Zero)

Sub standard()
For x = 142 To 11 Step -1
If Cells(x, "B").Value <> "" And Cells(x, "B").Value = 0 Then
Rows(x).EntireRow.Delete
End If
Next
End Sub

Mike