Home |
Search |
Today's Posts |
#5
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hey thanks Ron! It's that CountA function I was most unfamiliar with.
That's really handy! And you're right. Yours runs WAY faster than mine. :) Thanks for taking the time to explain. Max. Ron de Bruin wrote: Hi Max Sub Example() Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long Dim StartRow As Long Dim EndRow As Long ' Turn off screenupdating and save calc setting and change it to manual ' You code run faster then With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ' set to normal view if you are in page break view, also for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Loop from row 1000 -1 in steps of -1 With ActiveSheet .DisplayPageBreaks = False StartRow = 1 EndRow = 1000 For Lrow = EndRow To StartRow Step -1 'We use the worksheetfunction counta to see if there is somthing in A:C range of the row in the loop 'If 0 then all cells are empty If Application.CountA(.Range(.Cells(Lrow, "A"), .Cells(Lrow, "C"))) = 0 Then .Rows(Lrow).Delete Next End With 'Restore settings ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message oups.com... Hey, that's pretty slick, Ron. Would you mind posting it again with some line comments? I'm not familiar with some of the properties and methods you called. I'd like to learn the ideas behind what you've done here. Max. Ron de Bruin wrote: You can do it like this without selecting 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 = 1 EndRow = 1000 For Lrow = EndRow To StartRow Step -1 If Application.CountA(.Range(.Cells(Lrow, "A"), .Cells(Lrow, "C"))) = 0 Then .Rows(Lrow).Delete Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Can I delete blank rows from excel without selecting them? | Excel Worksheet Functions | |||
Excel Novice: Delete blank rows | Excel Discussion (Misc queries) | |||
delete blank rows | Excel Discussion (Misc queries) | |||
How to delete blank rows | Excel Discussion (Misc queries) | |||
blank cell turns to 0 | New Users to Excel |