Delete rows using VB
 

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

Delete rows using VB
 

How about a nice for each loop that does NOT use SELECT. Or use
datafilterfilter for 0delete. 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

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

Delete rows using VB
 

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

Delete rows using VB
 

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

Delete rows using VB
 

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