Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help revising slow code
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help revising slow code
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 -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fast code in 2003 = agonizingly slow code in 2007 | Excel Programming | |||
excel 2003 - revising a formula to make it more accurate | Excel Worksheet Functions | |||
Slow code when used as VBA code instead of macro (copying visible columns) | Excel Programming | |||
need help revising | Excel Programming | |||
Need help revising print range selection | Excel Programming |