remove all blank or empty rows

  • Thread starter Thread starter lowrey_nor
  • Start date Start date
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
 
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
 
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
 
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
 
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
 
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)




"(e-mail address removed)" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
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
 
Back
Top