Deleting empty rows
 

Good afternoon all

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

Thanks for your time
Foss

Deleting empty rows
 

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


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


"Foss" wrote in message ...
Good afternoon all,

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

Thanks for your time,
Foss

Deleting empty rows
 

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.

--
Regards,
Tom Ogilvy


"Ron de Bruin" wrote in message
...
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


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


"Foss" wrote in message
...
Good afternoon all,

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

Thanks for your time,
Foss

Deleting empty rows
 

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

--
Regards
Tom Ogilv


"Ron de Bruin" wrote in messag
..
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 (al
columns
Nex
End Wit
With Applicatio
.ScreenUpdating = Tru
.Calculation = CalcMod
End Wit
End Su
--
Regards Ron de Brui
http://www.rondebruin.n
"Foss" wrote in messag
..
Good afternoon all
Does anyone have something nice and quick that can delete all empty row
in the UsedRange
Thanks for your time
Fos

Deleting empty rows
 

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


--
Regards Ron de Brui
http://www.rondebruin.n


"Foss" wrote in message ..
Good afternoon all
Does anyone have something nice and quick that can delete all empty rows in the UsedRange
Thanks for your time
Fos