remove all blank or empty rows

[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

 

[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

 

[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

 

[lowrey_nor]

That worked Great Thanks Very much I work on and off for
two days on that

 

[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

 

[lowrey_nor]

Thanks Tom for your input and that would work but I'm not
sure how to use it

 

[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

 

[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

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)




"(e-mail address removed)" <[email protected]> wrote in

message news:[email protected]...

 

[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