Delete Blank Rows
 

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

Delete Blank Rows
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"ir26121973" wrote in message ...
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

Delete Blank Rows
 

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" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"ir26121973" wrote in message ...
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

Delete Blank Rows
 

Hi Chris

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"ir26121973" wrote in message ...
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" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"ir26121973" wrote in message ...
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