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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Miguel" <(E-Mail Removed)> wrote in message
news:14B168EA-F2EF-4E8E-AF69-(E-Mail Removed)...
> 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

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

"Miguel" wrote:

> 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

There is no option in SpecialCells for a zero

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Miguel" <(E-Mail Removed)> wrote in message news:14B168EA-F2EF-4E8E-AF69-(E-Mail Removed)...
> 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

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

"Mike H" wrote:

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