Help with Chip Pearson's Code for Deleting Blank Rows
 

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).E ntireRow) = 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

Help with Chip Pearson's Code for Deleting Blank Rows
 

"Rashid Khan" wrote in message
...
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).E ntireRow) = 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

Help with Chip Pearson's Code for Deleting Blank Rows
 

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).E ntireRow) = 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 Khan" wrote in message
...
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).E ntireRow) = 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

Help with Chip Pearson's Code for Deleting Blank Rows
 

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

"Rashid Khan" wrote in message
...
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).E ntireRow) = 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

Help with Chip Pearson's Code for Deleting Blank Rows
 

Sorry, I misread the question, (Cower!)
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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

"Rashid Khan" wrote in message
...
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).E ntireRow) = 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

Help with Chip Pearson's Code for Deleting Blank Rows
 

Alan,

We all do that from time to time....

Bernie

"Alan" wrote in message
...
Sorry, I misread the question, (Cower!)

Thanks! Help with Chip Pearson's Code for Deleting Blank Rows
 

Hello Bernie and Alan
Thanks a lot... It works fine now.

Rashid Khan
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Alan,

We all do that from time to time....

Bernie

"Alan" wrote in message
...
Sorry, I misread the question, (Cower!)