Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop all sheetsand delete empty rows
Hi There,
Underneath macro works fine to delete the empty rows in my used-range on the active sheet. I want to run this procedure on all my sheets ... but the looping fails! While looping the sheet does not become active? Sub DeleteEmptyRows() Dim LastRow As Long Dim l As Long Dim wsh As Worksheet On Error GoTo err Application.ScreenUpdating = False 'For Each wsh In ActiveWorkbook.Worksheets ' With wsh LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count Application.ScreenUpdating = False For l = LastRow To 1 Step -1 If Application.WorksheetFunction.CountA(Rows(l)) = 0 _ Then Rows(l).Delete Next l ' End With 'Next wsh err: End Sub Hope you can help, Sige |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop all sheetsand delete empty rows
Sub DeleteEmptyRows()
Dim LastRow As Long Dim l As Long Dim wsh As Worksheet On Error GoTo err Application.ScreenUpdating = False For Each wsh In ActiveWorkbook.Worksheets With wsh LastRow = .UsedRange.Row - 1 + .UsedRange.Rows.Count For l = LastRow To 1 Step -1 If Application.WorksheetFunction.CountA(.Rows(l)) _ = 0 Then .Rows(l).Delete Next l End With Next wsh err: Application.ScreenUpdating = True End Sub -- HTH Bob Phillips "Sige" wrote in message oups.com... Hi There, Underneath macro works fine to delete the empty rows in my used-range on the active sheet. I want to run this procedure on all my sheets ... but the looping fails! While looping the sheet does not become active? Sub DeleteEmptyRows() Dim LastRow As Long Dim l As Long Dim wsh As Worksheet On Error GoTo err Application.ScreenUpdating = False 'For Each wsh In ActiveWorkbook.Worksheets ' With wsh LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count Application.ScreenUpdating = False For l = LastRow To 1 Step -1 If Application.WorksheetFunction.CountA(Rows(l)) = 0 _ Then Rows(l).Delete Next l ' End With 'Next wsh err: End Sub Hope you can help, Sige |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop all sheetsand delete empty rows
Thx Bob!
"NOSPAM" to be removed for direct mailing... *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hpw do I delete multiple empty rows found between filled rows? | Excel Worksheet Functions | |||
How to Delete empty rows in excel in b/w rows with values | Excel Worksheet Functions | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
loop to delete rows... | Excel Programming | |||
How do I delete rows and columns in With With End Loop? | Excel Programming |