Help revising slow code

[Dani]

I am using the following code to remove all the lines in a worksheet that
have a zero value in Column H. Because there are thousands of rows in the
worksheet, this loop takes forever! Does anyone have any suggestions for
doing this more quickly? Thanks!

Sub Delete_Row_w0()
Dim Firstrow As Long
Dim LastRow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

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

With Sheets(1)
.Select
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
.DisplayPageBreaks = False
Firstrow = .UsedRange.Cells(1).Row
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
For Lrow = LastRow To Firstrow Step -1

With .Cells(Lrow, "H")

If Not IsError(.Value) Then

If .Value = 0 Then .EntireRow.Delete

End If

End With

Next Lrow

End With

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

End Sub

 

[Rick Rothstein]

The following suggested macros assume Column H contains constant values and
not formulas that display values. With that said, IF the cells in Column H
will NEVER be blank when there is data in other cells on that row, then you
can use this macro...

Sub RemoveZeroesFromColumnHAsLongAsThereAreNoBlanksInColumnH()
With Columns("H")
.Replace "0", "", xlWhole
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub

If, however, there could be blanks in Column H on rows that have data
elsewhere, then try this macro instead...

Sub RemoveZeroesFromColumnH()
Dim LastRow As Long
LastRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
With Range("H1:H" & LastRow)
.Replace "", Chr(255), xlWhole
.Replace "0", "", xlWhole
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.Replace Chr(255), "", xlWhole
End With
End Sub

As with all macros you are trying out for the first time, it is advisable to
do your test on a copy of your worksheet and not on the actual worksheet
containing your data... changes made by a macro cannot be undone.

 

[Rick Rothstein]

One thing I forgot... we need to protect the code against there being no
zeroes in Column H. Put the following statement in as the FIRST line of code
in whichever macro of mine that you use...

On Error Resume Next