Deleting empty rows

[Guest]

Good afternoon all

Does anyone have something nice and quick that can delete all empty rows in the UsedRange

Thanks for your time
Foss

 

[Ron de Bruin]

Try this Foss

Sub Example1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

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

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(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delete
'This will delete the row if the whole row is empty (all columns)

Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

 

[Tom Ogilvy]

If you can make the determination on a single column (if a cell in that
column is blank, then delete) and the cells are actually blank, not just
appear blank but contain a formula, and the number of non-contiguous areas
are less thant 8192 you can quickly delete with

On Error Resume Next
Columns(1).Specialcells(xlBlanks).EntireRow.Delete
On Error goto 0

1 specifies column A, adjust as needed.

 

[Guest]

Hi Tom

Thanks for the tip, that's a handy one

Cheers
Fos

----- Tom Ogilvy wrote: ----

If you can make the determination on a single column (if a cell in tha
column is blank, then delete) and the cells are actually blank, not jus
appear blank but contain a formula, and the number of non-contiguous area
are less thant 8192 you can quickly delete wit

On Error Resume Nex
Columns(1).Specialcells(xlBlanks).EntireRow.Delet
On Error goto

1 specifies column A, adjust as needed

 

[Guest]

Hi Ron

Thanks very much, that worked a treat

Cheers
Fos

----- Ron de Bruin wrote: ----

Try this Fos

Sub Example1(
Dim Firstrow As Lon
Dim Lastrow As Lon
Dim Lrow As Lon
Dim CalcMode As Lon

With Applicatio
CalcMode = .Calculatio
.Calculation = xlCalculationManua
.ScreenUpdating = Fals
End Wit

Firstrow = ActiveSheet.UsedRange.Cells(1).Ro
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow -
With ActiveShee
.DisplayPageBreaks = Fals
For Lrow = Lastrow To Firstrow Step -

If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delet
'This will delete the row if the whole row is empty (all columns

Nex
End Wit
With Applicatio
.ScreenUpdating = Tru
.Calculation = CalcMod
End Wit
End Su