Removing Blank Rows for Excel VB Newb

[Guest]

How can I create a macro or code to delete rows? I need it to delete all the
rows with absolutely nothing in them, so basing it on one column won't work.
Also, a lot of the cells are merged throughout.

I am still a little confused about the different drop down choices when
looking at the code area, so if you would please explain what area I would
need to copy to (if you post code). Is it possible to have it where this
macro or code is available whenever I open excel? I do a lot of on-line
reporting in Excel format and it would be a great help to have the macro
available when I first open the reports.

Thanks for your help,

Joel

 

[Ron de Bruin]

Hi Joel

You copy the code in a standard module
http://www.rondebruin.nl/code.htm

Try this macro for the data on the activesheet
More on this page http://www.rondebruin.nl/delete.htm

Sub Loop_Example()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'We use the ActiveSheet but you can replace this with
'Sheets("MySheet")if you want
With ActiveSheet

'We select the sheet so we can change the window view
.Select

'If you are in Page Break Preview Or Page Layout view go
'back to normal view, we do this for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

'Turn off Page Breaks, we do this for speed
.DisplayPageBreaks = False

'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1

If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delete

Next Lrow

End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub