Deleting a row that has a zero in column A

[Bob]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Mark Ivey]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Bob]

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

Thanks.

 

[Héctor Miguel]

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.

 

[Bob]

Thanks - the loop macro worked fine.
--
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

 

[Mark Ivey]

Glad it worked out...

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

Mark

Thanks - the loop macro worked fine.

 

[Dana DeLouis]

.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