Deleting rows (with zeros) with a macro

[Scott R]

Hi Guys im trying to delete empty rows with a macro using the following vba
however the rows have been copied from another sheet and pasted as values
that were from formulas so the 'empty' rows actually contain a lot of "0"'s.
the code im using only seems to delete rows with nothing in the row at all.
is there a way to tell the macro to delete rows that have zeros in them?

Thanks heaps

scott

Sub Example2()
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim StartRow As Long
Dim EndRow As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 2
EndRow = 253

For Lrow = EndRow To StartRow Step -1

If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).delete
'This will delete the row if the whole row is empty (all columns)

Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

 

[ryguy7272]

Which column are you in? See my code below...

'This subroutine will delete an entire row if the value in a certain column,
'in this case column "T", is blank.
Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "T").Value = "" Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub


'This macro will delete all of the blank rows in the active
'worksheet or in the selection. If the current selection
'covers more than one row, only blank rows in those rows
'will be deleted. Otherwise, all blank rows in the entire
'worksheet will be deleted. The entire row must be blank
'for the row to be deleted.
Public Sub DeleteBlankRows()
Dim R As Long
Dim C As Range
Dim Rng As Range
On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).EntireRow) = 0 Then
Rng.Rows(R).EntireRow.Delete
End If
Next R
EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

HTH,
Ryan---

 

[Scott R]

Hi ryan, some columns have zeros in them and some are blank but i would need
the entire row in that instance deleted, im not sure if your code would work?

 

[ryguy7272]

Well, what is the logic? You have to tell me the logic for me to give you
the right code. A quick solution would be to filter for zeros, or blanks, on
the applicable columns, then delete those, unhide, and sort ascending, or
descending, again depedning on what is applicable. That's a viable solution
if this is a one-off. Code is great if you have to do this over, and over,
and over, week after week or day after day.

HTH,
Ryan---

 

[Scott R]

sorry if im a bit vague i dont use code that often but this will be a high
use workbook so definitely need code..
some rows will have data in them (which i want to keep) and others will only
a combination of zeros and blank cells (across columns A to AC) so i want to
be able to run a macro that will delete those rows to leave only the rows
that have data in them.

 

[Scott R]

Hi Ryan, i figured it out. i can use your code so that if the 1st column is a
zero that row can be deleted.. thansk for your help..

 

[RyGuy]

Awesome! Sometimes it just take a little time for something to click. When
I don't see an obvious answer, I step away from the computer for a bit. When
I come back, with fresh eyes, a solution usually presents itself. Sounds
like that's what happened here...

Ryan---

 

[ToddS]

Ryan -

I used your 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

As you can see, I simply added a zero for the Value. 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 obtained via a formula?