Delete rows using VB

M

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
 
D

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
 
M

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
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top