remove all blank or empty rows
 

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

remove all blank or empty rows
 

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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



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

remove all blank or empty rows
 

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



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



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

remove all blank or empty rows
 

That worked Great Thanks Very much I work on and off for
two days on that
-----Original Message-----
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



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



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





.

remove all blank or empty rows
 

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

--
Regards,
Tom Ogilvy

wrote in
message ...
That worked Great Thanks Very much I work on and off for
two days on that
-----Original Message-----
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



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



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





.

remove all blank or empty rows
 

Thanks Tom for your input and that would work but I'm not
sure how to use it
-----Original Message-----
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.Delet e

is also a possibility

--
Regards,
Tom Ogilvy


wrote in
message ...
That worked Great Thanks Very much I work on and off for
two days on that
-----Original Message-----
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



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



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





.



.

remove all blank or empty rows
 

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)
www.rondebruin.nl



" wrote in message ...
Thanks Tom for your input and that would work but I'm not
sure how to use it
-----Original Message-----
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.Delet e

is also a possibility

--
Regards,
Tom Ogilvy


wrote in
message ...
That worked Great Thanks Very much I work on and off for
two days on that
-----Original Message-----
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



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



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





.



.

remove all blank or empty rows
 

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 wrote in message
...
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)
www.rondebruin.nl



" wrote in
message ...
Thanks Tom for your input and that would work but I'm not
sure how to use it
-----Original Message-----
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.Delet e

is also a possibility

--
Regards,
Tom Ogilvy


wrote in
message ...
That worked Great Thanks Very much I work on and off for
two days on that
-----Original Message-----
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



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



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





.



.

remove all blank or empty rows
 

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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



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



" wrote in message ...
Thanks Tom for your input and that would work but I'm not
sure how to use it
-----Original Message-----
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.Delet e

is also a possibility

--
Regards,
Tom Ogilvy


wrote in
message ...
That worked Great Thanks Very much I work on and off for
two days on that
-----Original Message-----
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



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



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





.



.