Delete Blank Rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
Back
Top