Delete Blank Rows

[Guest]

Hi,

Wonder if someone could help me please.

I have a spreadsheet with various rows of data in. There are however rows
that are blank.

Can anyone point me in the right direction as to how I may write a macro
that searches from row 1 to 65536 and checks that if the cells in columns A:Z
are blank it deletes that row.

Thanks very much

Chris

 

[Ron de Bruin]

Test this one Chris

Sub Example1()
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

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1

With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1

If Application.CountA(.Range(.Cells(Lrow, "A"), _
.Cells(Lrow, "Z"))) = 0 Then .Rows(Lrow).Delete

Next
End With

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

End Sub

 

[Guest]

Ron,

This works a treat, thanks very much.

Sorry it's taken a while to reply. Trying to get this diabolical web site to
work.

Once again many thanks

Chris

 

[Ron de Bruin]

Hi Chris

Use Outlook Express to connect to the newsgroups
Much faster and better