Deleting a row that has a zero in column A
 

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

Deleting a row that has a zero in column A
 

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

Deleting a row that has a zero in column A
 

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

Deleting a row that has a zero in column A
 

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

Deleting a row that has a zero in column A
 

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

Deleting a row that has a zero in column A
 

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

Deleting a row that has a zero in column A
 

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

Deleting a row that has a zero in column A
 

Glad it worked out...

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

Mark

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

Deleting a row that has a zero in column A
 

.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)" wrote 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

"Rick Rothstein (MVP - VB)" wrote in
message ...
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" wrote in message
...
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