Delete entire row if column B shows -

[FJ]

Hi, I'm trying to write a macro that will delete an entire row if the value
of the cell in column B of that row is 0. In this case, the number format is
set to display zeros as dashes. I'm not sure if that would make a difference
in the way the macro is written. Anyway, I've tried the following with no
success. Can anyone tell me where I'm going wrong?

Range("b:b").SpecialCells(xlCellTypeAllFormatConditions, 0).EntireRow.Delete

I'm very new to VBA so I might be totally offbase with this to begin with.

Thanks in advance for any help.

 

[Mark Ivey]

Take a look at F. David McRitchie's site...

http://www.mvps.org/dmcritchie/excel/delempty.htm

 

[Don Guillett]

Try this. Test to see if rows selected>then change .select to .delete
Sub delzerorows()
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("A1:F" & lr).AutoFilter Field:=2, Criteria1:="0"
Range("A2:F" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Select
Range("a1:f" & lr).AutoFilter
End Sub

 

[FJ]

Hi, Mark, thank you very much for the link. There is a lot of great
information on that site and I was able to find a macro that did what I
needed. Thanks again!

 

[FJ]

Hi, Don, thank you very much for that code. I tried it and and it worked
great.

Thanks again!

 

[Don Guillett]

Glad to help

 

[global_india]

Hi, I'm trying to write a macro that will delete an entire row if the value
of the cell in column B of that row is 0. In this case, the number format is
set to display zeros as dashes. I'm not sure if that would make a difference
in the way the macro is written. Anyway, I've tried the following with no
success. Can anyone tell me where I'm going wrong?

Range("b:b").SpecialCells(xlCellTypeAllFormatConditions, 0).EntireRow.Delete

I'm very new to VBA so I might be totally offbase with this to begin with.

Thanks in advance for any help.

Sub testing()
Dim rCount As Long
Dim cCount As Long

With Sheet4
rCount = Sheet4.Cells(.Rows.Count, "B").End(xlUp).Row
End With

Dim a As Long
For a = 1 To rCount
If Sheet4.Cells(a, 2).Value = 0 Then
Sheet4.Cells(a, 2).EntireRow.Delete
End If
Next a
End Sub