Help with Chip Pearson's Code for Deleting Blank Rows

  • Thread starter Thread starter Rashid Khan
  • Start date Start date
R

Rashid Khan

Hello All,
I have obtained the following macros from Chip Pearson's Website which
deletes BlankRows.... and it is excellent.

Public Sub DeleteBlankRows()'chip PearsonDim R As LongDim C As RangeDim N As
LongDim rng As Range On Error GoTo EndMacroApplication.ScreenUpdating =
FalseApplication.Calculation = xlCalculationManual If Selection.Rows.Count >
1 Then Set rng = SelectionElse Set rng = ActiveSheet.UsedRange.RowsEnd
IfN = 0For R = rng.Rows.Count To 1 Step -1 If
Application.WorksheetFunction.CountA(rng.Rows(R).EntireRow) = 0 Then
rng.Rows(R).EntireRow.Delete N = N + 1 End IfNext
REndMacro:Application.ScreenUpdating = TrueApplication.Calculation =
xlCalculationAutomaticEnd Sub
But I wish it to run the above macro on all the Sheets in my Workbook.
Somethin like.
For z = 1 To Sheets.Count
' Sheets(z).Activate
' do Row Deleting here
'Next

But I have failed in all experiments.. Can somebody suggest a way of how to
implement Chip Pearson's macro to run on all the Worksheets in the Workbook
 
Rashid Khan said:
Hello All,
I have obtained the following macros from Chip Pearson's Website which
deletes BlankRows.... and it is excellent.

Public Sub DeleteBlankRows()'chip PearsonDim R As LongDim C As RangeDim N As
LongDim rng As Range On Error GoTo EndMacroApplication.ScreenUpdating =
FalseApplication.Calculation = xlCalculationManual If Selection.Rows.Count

1 Then Set rng = SelectionElse Set rng = ActiveSheet.UsedRange.RowsEnd
IfN = 0For R = rng.Rows.Count To 1 Step -1 If
Application.WorksheetFunction.CountA(rng.Rows(R).EntireRow) = 0 Then
rng.Rows(R).EntireRow.Delete N = N + 1 End IfNext
REndMacro:Application.ScreenUpdating = TrueApplication.Calculation =
xlCalculationAutomaticEnd Sub
But I wish it to run the above macro on all the Sheets in my Workbook.
Somethin like.
For z = 1 To Sheets.Count
' Sheets(z).Activate
' do Row Deleting here
'Next

But I have failed in all experiments.. Can somebody suggest a way of how to
implement Chip Pearson's macro to run on all the Worksheets in the Workbook
 
Hi,
This is Chips code, NOT mine I hasten to add, He is a little bit better at
it than me than me!!

Public Sub DeleteBlankRows()
Dim R As Long
Dim C As Range
Dim Rng As Range
On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).EntireRow) = 0 Then
Rng.Rows(R).EntireRow.Delete
End If
Next R
EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Hit alt and F11
On the toolbar, click 'Insert'
Click 'Module' (not Class module)
Copy Chips code and paste it into the module, watch out for text wrap,
Thats it,
alt and F11 again to close the VB editor,
Tools > Macros > Select > DeleteBlankRows()
Hit 'Run'

Regards,
 
Rashid,

Put this in the same module, then run it

Sub DoAllSheets()

Dim mySht As Worksheet

For Each mySht In Activeworkbook.Worksheets
mySht.Select
mySht.UsedRange.Select
DeleteBlankRows
Next mySht
End Sub


HTH,
Bernie
MS Excel MVP
 
Sorry, I misread the question, (Cower!)
Bernie Deitrick said:
Rashid,

Put this in the same module, then run it

Sub DoAllSheets()

Dim mySht As Worksheet

For Each mySht In Activeworkbook.Worksheets
mySht.Select
mySht.UsedRange.Select
DeleteBlankRows
Next mySht
End Sub


HTH,
Bernie
MS Excel MVP

N
 
Back
Top