Removing Empty Rows On Multiple Sheets With Different Ranges

Hi,

I currently use the following code to remove empty rows :

Please Login or Register  to view this content.

I first delete the formula from the first cell if the cell is empty and then I delete the entire row if the first cell is empty.

This works just fine.

However, I would like to do this for multiple worksheets. The sheets all have different names (not sheet1, sheet2, etc) and the ranges are not always the same (I don't want to apply it to the entire worksheet, only to a specific range).

I've tried using union but that doesn't work.

Any suggestions on how to do this?

Thanks in advance.

Steven

Your code could be reduced to:

Please Login or Register  to view this content.

What determines the range - where is it stored?

Originally Posted by StephenR

Your code could be reduced to:

Please Login or Register  to view this content.

What determines the range - where is it stored?

Like I said above. The cell isn't empty. It contains a formula, so I have to delete that formula first. But that's not the point.

The name of sheets and the ranges are determined by me, so they can be "stored" in the code.

Steven

Why not just repeat your code and change the sheet name and range?

Originally Posted by StephenR

Why not just repeat your code and change the sheet name and range?

Are you serious?

It's not something I have to do once. Every time new data is entered, I want to have the ability to remove the empty rows in the output tables (used for reporting purposes).

So, one button, one macro...and not 10 buttons...

Hope this makes sense...

Steven

Perhaps you should lose the attitude.

Originally Posted by StephenR

Perhaps you should lose the attitude.

Very helpful, thanks.

Hi,

I also want to a similar thing. I have workbook with multiple worksheets and want to delete any blank rows in all the sheets.

I have found the following which deletes the rows in the active sheet, any idea how I can get it to do all sheets:

Sub DeleteBlankRows1()

'Deletes the entire row within the selection if the ENTIRE row contains no data.



'We use Long in case they have over 32,767 rows selected.

Dim i As Long



'We turn off calculation and screenupdating to speed up the macro.

With Application

.Calculation = xlCalculationManual

.ScreenUpdating = False



'We work backwards because we are deleting rows.

For i = Selection.Rows.Count To 1 Step -1

If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then

Selection.Rows(i).EntireRow.Delete

End If

Next i



.Calculation = xlCalculationAutomatic

.ScreenUpdating = True

End With

End Sub