I would like to know how I would build a macro that deletes an entire row
that has a zero in column A. For example, my column A has values greater
than 0 until the end of the report and then the values are all be zeros:

16
10
8
7
5
0
0
0

I would like a macro that deletes the entire row that has a zero in column A.

Thanks.

Bob


--
Bob

Give this macro a try...

Sub HideRowIfZeroInA()
Dim R As Range
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For Each R In .Range("A3:A" & CStr(LastRow))
If R.Value = 0 And R.Value <> "" Then R.EntireRow.Hidden = True
Next
End With
End Sub

Note: Change the reference to Sheet1 (keep the quote marks) in the
With statement to the actual sheet name you want to hide the
rows on.

Rick


"Bob" <(E-Mail Removed)> wrote in message
news:049B33DD-A92F-43F2-A99B-(E-Mail Removed)...
>I would like to know how I would build a macro that deletes an entire row
> that has a zero in column A. For example, my column A has values greater
> than 0 until the end of the report and then the values are all be zeros:
>
> 16
> 10
> 8
> 7
> 5
> 0
> 0
> 0
>
> I would like a macro that deletes the entire row that has a zero in column
> A.
>
> Thanks.
>
> Bob
>
>
> --
> Bob

Take a look at the following website:
http://www.mvps.org/dmcritchie/excel/delempty.htm

Go down until you see the information for:
Delete ALL rows that have cell in Column A that looks blank

This is a very mildly altered version from David's site to delete rows in
column A that contain a ZERO:
Sub DeleteRowsThatLookEmptyinColA()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'pre XL97 xlManual
Dim Rng As Range, ix As Long
Set Rng = Intersect(Range("A:A"), ActiveSheet.UsedRange)
For ix = Rng.Count To 1 Step -1
If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = "0" Then
Rng.Item(ix).EntireRow.Delete
End If
Next
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Mark

"Bob" <(E-Mail Removed)> wrote in message
news:049B33DD-A92F-43F2-A99B-(E-Mail Removed)...
> I would like to know how I would build a macro that deletes an entire row
> that has a zero in column A. For example, my column A has values greater
> than 0 until the end of the report and then the values are all be zeros:
>
> 16
> 10
> 8
> 7
> 5
> 0
> 0
> 0
>
> I would like a macro that deletes the entire row that has a zero in column
> A.
>
> Thanks.
>
> Bob
>
>
> --
> Bob

Sorry, I grabbed a previous response to a similar question (only it wanted
to hide, not delete, the rows) and did a terrible job of modifying it for
your question. Here is the code I should have posted...

Sub DeleteRowIfZeroInA()
Dim X As Long
Dim R As Range
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For X = LastRow To 1 Step -1
If .Cells(X, "A").Value = 0 And .Cells(X, "A").Value <> "" Then
.Cells(X, "A").EntireRow.Delete xlShiftUp
End If
Next
End With
End Sub

Rick

"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> Give this macro a try...
>
> Sub HideRowIfZeroInA()
> Dim R As Range
> Dim LastRow As Long
> With Worksheets("Sheet1")
> LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
> For Each R In .Range("A3:A" & CStr(LastRow))
> If R.Value = 0 And R.Value <> "" Then R.EntireRow.Hidden = True
> Next
> End With
> End Sub
>
> Note: Change the reference to Sheet1 (keep the quote marks) in the
> With statement to the actual sheet name you want to hide the
> rows on.
>
> Rick
>
>
> "Bob" <(E-Mail Removed)> wrote in message
> news:049B33DD-A92F-43F2-A99B-(E-Mail Removed)...
>>I would like to know how I would build a macro that deletes an entire row
>> that has a zero in column A. For example, my column A has values greater
>> than 0 until the end of the report and then the values are all be zeros:
>>
>> 16
>> 10
>> 8
>> 7
>> 5
>> 0
>> 0
>> 0
>>
>> I would like a macro that deletes the entire row that has a zero in
>> column A.
>>
>> Thanks.
>>
>> Bob
>>
>>
>> --
>> Bob
>

Thanks Rick. Would I be able to reference more than 1 sheet in this macro i.e
(Sheet1, Sheet2 etc)?

Thanks.
--
Bob


"Rick Rothstein (MVP - VB)" wrote:

> Sorry, I grabbed a previous response to a similar question (only it wanted
> to hide, not delete, the rows) and did a terrible job of modifying it for
> your question. Here is the code I should have posted...
>
> Sub DeleteRowIfZeroInA()
> Dim X As Long
> Dim R As Range
> Dim LastRow As Long
> With Worksheets("Sheet1")
> LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
> For X = LastRow To 1 Step -1
> If .Cells(X, "A").Value = 0 And .Cells(X, "A").Value <> "" Then
> .Cells(X, "A").EntireRow.Delete xlShiftUp
> End If
> Next
> End With
> End Sub
>
> Rick
>
> "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
> message news:(E-Mail Removed)...
> > Give this macro a try...
> >
> > Sub HideRowIfZeroInA()
> > Dim R As Range
> > Dim LastRow As Long
> > With Worksheets("Sheet1")
> > LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
> > For Each R In .Range("A3:A" & CStr(LastRow))
> > If R.Value = 0 And R.Value <> "" Then R.EntireRow.Hidden = True
> > Next
> > End With
> > End Sub
> >
> > Note: Change the reference to Sheet1 (keep the quote marks) in the
> > With statement to the actual sheet name you want to hide the
> > rows on.
> >
> > Rick
> >
> >
> > "Bob" <(E-Mail Removed)> wrote in message
> > news:049B33DD-A92F-43F2-A99B-(E-Mail Removed)...
> >>I would like to know how I would build a macro that deletes an entire row
> >> that has a zero in column A. For example, my column A has values greater
> >> than 0 until the end of the report and then the values are all be zeros:
> >>
> >> 16
> >> 10
> >> 8
> >> 7
> >> 5
> >> 0
> >> 0
> >> 0
> >>
> >> I would like a macro that deletes the entire row that has a zero in
> >> column A.
> >>
> >> Thanks.
> >>
> >> Bob
> >>
> >>
> >> --
> >> Bob
> >
>
>

hi, guys !

> Bob wrote in message ...
> Thanks Rick. Would I be able to reference more than 1 sheet in this macro i.e (Sheet1, Sheet2 etc)?

you might want to give a try to a differente approach
using autofilter allows to delete rows in a single step
and assuming row1 [A1] has a title (i.e.)

Sub DeleteRowIfZeroInA_v2()
Dim WS As Worksheet
For Each WS In Worksheets(Array("sheet1", "sheet2", "sheet 5"))
With WS.Range(WS.[a1], WS.[a65536].End(xlUp))
If Application.CountIf(.Offset(), 0) Then
.AutoFilter 1, 0
.Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
.AutoFilter
End If
End With
Next
End Sub

hth,
hector.

>> Rick Rothstein wote in message ...
>> Sorry, I grabbed a previous response to a similar question (only it wanted to hide, not delete, the rows)
>> and did a terrible job of modifying it for your question. Here is the code I should have posted...
>>
>> Sub DeleteRowIfZeroInA()
>> Dim X As Long
>> Dim R As Range
>> Dim LastRow As Long
>> With Worksheets("Sheet1")
>> LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
>> For X = LastRow To 1 Step -1
>> If .Cells(X, "A").Value = 0 And .Cells(X, "A").Value <> "" Then
>> .Cells(X, "A").EntireRow.Delete xlShiftUp
>> End If
>> Next
>> End With
>> End Sub
>>
>> Rick

Thanks - the loop macro worked fine.
--
Bob


"Mark Ivey" wrote:

> Take a look at the following website:
> http://www.mvps.org/dmcritchie/excel/delempty.htm
>
> Go down until you see the information for:
> Delete ALL rows that have cell in Column A that looks blank
>
> This is a very mildly altered version from David's site to delete rows in
> column A that contain a ZERO:
> Sub DeleteRowsThatLookEmptyinColA()
> Application.ScreenUpdating = False
> Application.Calculation = xlCalculationManual 'pre XL97 xlManual
> Dim Rng As Range, ix As Long
> Set Rng = Intersect(Range("A:A"), ActiveSheet.UsedRange)
> For ix = Rng.Count To 1 Step -1
> If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = "0" Then
> Rng.Item(ix).EntireRow.Delete
> End If
> Next
> done:
> Application.Calculation = xlCalculationAutomatic
> Application.ScreenUpdating = True
> End Sub
>
> Mark
>
> "Bob" <(E-Mail Removed)> wrote in message
> news:049B33DD-A92F-43F2-A99B-(E-Mail Removed)...
> > I would like to know how I would build a macro that deletes an entire row
> > that has a zero in column A. For example, my column A has values greater
> > than 0 until the end of the report and then the values are all be zeros:
> >
> > 16
> > 10
> > 8
> > 7
> > 5
> > 0
> > 0
> > 0
> >
> > I would like a macro that deletes the entire row that has a zero in column
> > A.
> >
> > Thanks.
> >
> > Bob
> >
> >
> > --
> > Bob
>

Glad it worked out...

Please thank David McRitchie for his help as well. This macro came from his
site.

Mark

"Bob" <(E-Mail Removed)> wrote in message
news:2D43007E-D255-415F-9C28-(E-Mail Removed)...
> Thanks - the loop macro worked fine.
> --
> Bob
>
>
> "Mark Ivey" wrote:
>
>> Take a look at the following website:
>> http://www.mvps.org/dmcritchie/excel/delempty.htm
>>
>> Go down until you see the information for:
>> Delete ALL rows that have cell in Column A that looks blank
>>
>> This is a very mildly altered version from David's site to delete rows in
>> column A that contain a ZERO:
>> Sub DeleteRowsThatLookEmptyinColA()
>> Application.ScreenUpdating = False
>> Application.Calculation = xlCalculationManual 'pre XL97 xlManual
>> Dim Rng As Range, ix As Long
>> Set Rng = Intersect(Range("A:A"), ActiveSheet.UsedRange)
>> For ix = Rng.Count To 1 Step -1
>> If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = "0" Then
>> Rng.Item(ix).EntireRow.Delete
>> End If
>> Next
>> done:
>> Application.Calculation = xlCalculationAutomatic
>> Application.ScreenUpdating = True
>> End Sub
>>
>> Mark
>>
>> "Bob" <(E-Mail Removed)> wrote in message
>> news:049B33DD-A92F-43F2-A99B-(E-Mail Removed)...
>> > I would like to know how I would build a macro that deletes an entire
>> > row
>> > that has a zero in column A. For example, my column A has values
>> > greater
>> > than 0 until the end of the report and then the values are all be
>> > zeros:
>> >
>> > 16
>> > 10
>> > 8
>> > 7
>> > 5
>> > 0
>> > 0
>> > 0
>> >
>> > I would like a macro that deletes the entire row that has a zero in
>> > column
>> > A.
>> >
>> > Thanks.
>> >
>> > Bob
>> >
>> >
>> > --
>> > Bob
>>

> .Cells(X, "A").EntireRow.Delete xlShiftUp

Just to share a programming idea...
When Excel deletes an entire row, it knows it must Shift the next row up.

Rows(X).Delete

--
HTH :>)
Dana DeLouis


"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:ukO%(E-Mail Removed)...
> Sorry, I grabbed a previous response to a similar question (only it wanted
> to hide, not delete, the rows) and did a terrible job of modifying it for
> your question. Here is the code I should have posted...
>
> Sub DeleteRowIfZeroInA()
> Dim X As Long
> Dim R As Range
> Dim LastRow As Long
> With Worksheets("Sheet1")
> LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
> For X = LastRow To 1 Step -1
> If .Cells(X, "A").Value = 0 And .Cells(X, "A").Value <> "" Then
> .Cells(X, "A").EntireRow.Delete xlShiftUp
> End If
> Next
> End With
> End Sub
>
> Rick
>
> "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
> message news:(E-Mail Removed)...
>> Give this macro a try...
>>
>> Sub HideRowIfZeroInA()
>> Dim R As Range
>> Dim LastRow As Long
>> With Worksheets("Sheet1")
>> LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
>> For Each R In .Range("A3:A" & CStr(LastRow))
>> If R.Value = 0 And R.Value <> "" Then R.EntireRow.Hidden = True
>> Next
>> End With
>> End Sub
>>
>> Note: Change the reference to Sheet1 (keep the quote marks) in the
>> With statement to the actual sheet name you want to hide the
>> rows on.
>>
>> Rick
>>
>>
>> "Bob" <(E-Mail Removed)> wrote in message
>> news:049B33DD-A92F-43F2-A99B-(E-Mail Removed)...
>>>I would like to know how I would build a macro that deletes an entire row
>>> that has a zero in column A. For example, my column A has values
>>> greater
>>> than 0 until the end of the report and then the values are all be zeros:
>>>
>>> 16
>>> 10
>>> 8
>>> 7
>>> 5
>>> 0
>>> 0
>>> 0
>>>
>>> I would like a macro that deletes the entire row that has a zero in
>>> column A.
>>>
>>> Thanks.
>>>
>>> Bob
>>>
>>>
>>> --
>>> Bob
>>
>