Help revising slow code
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 RemoveZeroesFromColumnHAsLongAsThereAreNoBlanksInC olumnH()
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 (MVP - Excel)
"Dani" wrote in message
...
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
|