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.

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

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



"FJ" <(E-Mail Removed)> wrote in message
news:80B3995A-C2E1-4981-A43B-(E-Mail Removed)...
> 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.

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"FJ" <(E-Mail Removed)> wrote in message
news:80B3995A-C2E1-4981-A43B-(E-Mail Removed)...
> 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.

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!



"Mark Ivey" wrote:

> Take a look at F. David McRitchie's site...
>
> http://www.mvps.org/dmcritchie/excel/delempty.htm
>
>
>
> "FJ" <(E-Mail Removed)> wrote in message
> news:80B3995A-C2E1-4981-A43B-(E-Mail Removed)...
> > 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.
>

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

Thanks again!



"Don Guillett" wrote:

> 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
>
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "FJ" <(E-Mail Removed)> wrote in message
> news:80B3995A-C2E1-4981-A43B-(E-Mail Removed)...
> > 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.
>
>

Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"FJ" <(E-Mail Removed)> wrote in message
news:40D28442-99E1-4729-ABDE-(E-Mail Removed)...
> Hi, Don, thank you very much for that code. I tried it and and it worked
> great.
>
> Thanks again!
>
>
>
> "Don Guillett" wrote:
>
>> 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
>>
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "FJ" <(E-Mail Removed)> wrote in message
>> news:80B3995A-C2E1-4981-A43B-(E-Mail Removed)...
>> > 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.
>>
>>

[global_india]

Originally Posted by 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.

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