LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default To delete rows when more than one cell is blank

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I delete blank rows from excel without selecting them? rgtest Excel Worksheet Functions 9 February 14th 09 03:12 PM
Excel Novice: Delete blank rows Kezia Excel Discussion (Misc queries) 2 August 25th 06 04:56 PM
delete blank rows Pam C Excel Discussion (Misc queries) 1 January 17th 06 07:13 PM
How to delete blank rows John Mansfield Excel Discussion (Misc queries) 3 April 27th 05 11:48 PM
blank cell turns to 0 LMB New Users to Excel 2 April 25th 05 03:57 PM


All times are GMT +1. The time now is 04:29 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"