Deleting rows (with zeros) with a macro

T

ToddS

I used the macro below and it works perfectly except when I try to use it
for cells that contain formulas. Example:

Sub Delete Rows()
Dim RowNdx As Long
Dim LastRow As Long
StartRow = 2
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "F").Value = "0" Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub

My questions is, can I somehow modify this macro to recognize a zero value
when that value is not "hard coded" in the cell but populated via a formula?
 
M

Mike H

Hi,

Try this

Sub DeleteRows()
Dim RowNdx As Long
Dim LastRow As Long
StartRow = 2
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Not Cells(RowNdx, "F").HasFormula And _
Cells(RowNdx, "F").Value = 0 Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub

Mike
 
M

Mike H

Hi,

maybe I got it the wrong way around, use this if you want to delete formula
that evaluate as zero

Sub DeleteRows()
Dim RowNdx As Long
Dim LastRow As Long
StartRow = 2
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "F").HasFormula And _
Cells(RowNdx, "F").Value = 0 Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub

Mike
 
T

ToddS

PERFECT!!! Thank you VERY MUCH!!! (It was your second response that I
needed - thanks also for catching that)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top