remove all blank or empty rows

L

lowrey_nor

I'm new to VBA and I'm working on a project and leaning it
as I go. I'm at that point where I think I need a loop
that will loop through the rows and remove all blank or
empty rows. The worksheet will have a varying number of
rows and my have 1 to 4 blank or empty rows in a row (or
together)

Thanks to all who read this. Thanks to all who replay
 
R

Ron de Bruin

Try this for the activesheet
It will loop through all rows with data

Sub Example1()
Dim Lrow As Long
Dim CalcMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
For Lrow = .UsedRange.Rows.Count To 1 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)
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


I have add some example code on a webpage
http://www.rondebruin.nl/tips.htm

Post back if you need help
 
R

Ron de Bruin

Oops

Remove the End If

Sub Example1()
Dim Lrow As Long
Dim CalcMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
For Lrow = .UsedRange.Rows.Count To 1 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
 
T

Tom Ogilvy

Ron offered and excellent solution based on your description. Just to add:
if you can determine an empty row by having a blank cell in a single column

columns(1).SpecialCells(xlblanks).Entirerow.Delete

is also a possibility
 
R

Ron de Bruin

Tom's example will delete every row with a empty cell in column A
<columns(1) is the same as columns("A")>

I will not look if there are values in the other columns
 
T

Tom Ogilvy

As previously stated:

if you can determine an empty row by having a blank cell in a single column

columns(1).SpecialCells(xlblanks).Entirerow.Delete

is also a possibility

Sub DeleteBank()
columns(1).SpecialCells(xlblanks).Entirerow.Delete
End sub

is how you use it.

--
Regards,
Tom Ogilvy



Ron de Bruin said:
Tom's example will delete every row with a empty cell in column A
<columns(1) is the same as columns("A")>

I will not look if there are values in the other columns

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




"[email protected]" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
R

Ron de Bruin

If there are no Empty cells in column A you can use this to
avoid the error

Sub DeleteBank()
On Error Resume Next
Columns(1).SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0
End Sub
 
Top