Remove empty rows
 

Hi
Is it possible to write a macro that removes empty rows in a specified
range?
I hope someone can help.

Regards
Kaj Pedersen

Remove empty rows
 

Hi Kaj,
This has been answered very, very many times. If you do a Google search
within *.excel.* as a group, over just the last year, using "rows" as a
"Search for" criterion, you will come up with a variety of good
solutions.

HTH
Dave Braden

In article ,
"Kaj Pedersen" wrote:

Hi
Is it possible to write a macro that removes empty rows in a specified
range?
I hope someone can help.

Regards
Kaj Pedersen



--
(ROT13)

Remove empty rows
 

assume an empty row would have a blank cell in column A, other wise it would
not.

Columns(1).SpecialCells(xlBlanks).EntireRow.Delete

--
Regards,
Tom Ogilvy

"Kaj Pedersen" wrote in message
...
Hi
Is it possible to write a macro that removes empty rows in a specified
range?
I hope someone can help.

Regards
Kaj Pedersen

Remove empty rows
 

Or testing the whole row

Sub DeleteEmptyRows()
'JW
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For R = LastRow To 1 Step -1
If Application.CountA(Rows(R)) = 0 Then Rows(R).Delete
Next R
End Sub


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



"Tom Ogilvy" wrote in message ...
assume an empty row would have a blank cell in column A, other wise it would
not.

Columns(1).SpecialCells(xlBlanks).EntireRow.Delete

--
Regards,
Tom Ogilvy

"Kaj Pedersen" wrote in message
...
Hi
Is it possible to write a macro that removes empty rows in a specified
range?
I hope someone can help.

Regards
Kaj Pedersen

Remove empty rows
 

Ron,
This appears substantially faster than John's and Chip's approaches,
from test data I've devised. Can't say it's always faster.

Sub DeleteEmptyRows()
'Dave Braden
Dim l As Long, rng As Range, rngCol As Range, rngDel As Range

On Error Resume Next
Set rng = ActiveSheet.UsedRange
With rng.Columns
Set rngDel = .Item(1).SpecialCells(xlCellTypeBlanks).EntireRow
If rngDel Is Nothing Then Exit Sub
For l = 2 To .Count
Set rngDel = Intersect(rngDel, _
.Item(l).SpecialCells(xlCellTypeBlanks).EntireRow)
If rngDel Is Nothing Then Exit Sub
Next
End With
rngDel.Delete
End Sub

In article ,
"Ron de Bruin" wrote:

Or testing the whole row

Sub DeleteEmptyRows()
'JW
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For R = LastRow To 1 Step -1
If Application.CountA(Rows(R)) = 0 Then Rows(R).Delete
Next R
End Sub


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



"Tom Ogilvy" wrote in message
...
assume an empty row would have a blank cell in column A, other wise it
would
not.

Columns(1).SpecialCells(xlBlanks).EntireRow.Delete

--
Regards,
Tom Ogilvy

"Kaj Pedersen" wrote in message
...
Hi
Is it possible to write a macro that removes empty rows in a specified
range?
I hope someone can help.

Regards
Kaj Pedersen

--
(ROT13)

Remove empty rows
 

Hi David

I have save the sub to test it this weekend.
It looks good

Thanks for posting it


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



"David J. Braden" wrote in message ...
Ron,
This appears substantially faster than John's and Chip's approaches,
from test data I've devised. Can't say it's always faster.

Sub DeleteEmptyRows()
'Dave Braden
Dim l As Long, rng As Range, rngCol As Range, rngDel As Range

On Error Resume Next
Set rng = ActiveSheet.UsedRange
With rng.Columns
Set rngDel = .Item(1).SpecialCells(xlCellTypeBlanks).EntireRow
If rngDel Is Nothing Then Exit Sub
For l = 2 To .Count
Set rngDel = Intersect(rngDel, _
.Item(l).SpecialCells(xlCellTypeBlanks).EntireRow)
If rngDel Is Nothing Then Exit Sub
Next
End With
rngDel.Delete
End Sub

In article ,
"Ron de Bruin" wrote:

Or testing the whole row

Sub DeleteEmptyRows()
'JW
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For R = LastRow To 1 Step -1
If Application.CountA(Rows(R)) = 0 Then Rows(R).Delete
Next R
End Sub


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



"Tom Ogilvy" wrote in message
...
assume an empty row would have a blank cell in column A, other wise it
would
not.

Columns(1).SpecialCells(xlBlanks).EntireRow.Delete

--
Regards,
Tom Ogilvy

"Kaj Pedersen" wrote in message
...
Hi
Is it possible to write a macro that removes empty rows in a specified
range?
I hope someone can help.

Regards
Kaj Pedersen

--
(ROT13)

Remove empty rows
 

Ron,
Thanks for the promised test. As you know, I do this off of Mac/Unix, so
I can only hazard a guess how it will work out under Windows..

The other issue that raises its ugly head is "what is a blank cell"?

Idea (I learned from Myrna Larson) is to build up what you can, then do
the action to get the worksheet side of things to do the looping, as
that's generally much more efficient than looping with VBA through
worksheet stuff like this.

As you know, .Item(l).SpecialCells(xlCellTypeBlanks).EntireRow is the
entire row *within UsedRange* as opposed to having Excel check all 256
cells in a row.

Please post any improvements you can think of.

Also, I don't need one line I had, given the code structure, and had an
unneccesary Dim. So replace what I posted with:

Sub DeleteEmptyRows()
'Dave Braden
Dim l As Long, rng As Range, rngDel As Range

On Error Resume Next
Set rng = ActiveSheet.UsedRange
With rng.Columns
Set rngDel = .Item(1).SpecialCells(xlCellTypeBlanks).EntireRow

For l = 2 To .Count
Set rngDel = Intersect(rngDel, _
.Item(l).SpecialCells(xlCellTypeBlanks).EntireRow)
If rngDel Is Nothing Then Exit Sub
Next
End With
Application.ScreenUpdating = False
rngDel.Delete
End Sub

Could be there's a tradeoff as far as the sha[e of the worksheet; mine
almost always have far more rown than columns, so what I suggest will
likely be quite a lot faster, on average.

Regards,
Dave B

In article ,
"Ron de Bruin" wrote:

Hi David
I have save the sub to test it this weekend.
It looks good
Thanks for posting it
--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl

"David J. Braden" wrote in message
...
Ron,
This appears substantially faster than John's and Chip's approaches,
from test data I've devised. Can't say it's always faster.

Sub DeleteEmptyRows()
'Dave Braden
Dim l As Long, rng As Range, rngCol As Range, rngDel As Range

On Error Resume Next
Set rng = ActiveSheet.UsedRange
With rng.Columns
Set rngDel = .Item(1).SpecialCells(xlCellTypeBlanks).EntireRow
If rngDel Is Nothing Then Exit Sub
For l = 2 To .Count
Set rngDel = Intersect(rngDel, _
.Item(l).SpecialCells(xlCellTypeBlanks).EntireRow)
If rngDel Is Nothing Then Exit Sub
Next
End With
rngDel.Delete
End Sub

In article ,
"Ron de Bruin" wrote:

Or testing the whole row

Sub DeleteEmptyRows()
'JW
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For R = LastRow To 1 Step -1
If Application.CountA(Rows(R)) = 0 Then Rows(R).Delete
Next R
End Sub
--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl


"Tom Ogilvy" wrote in message
...
assume an empty row would have a blank cell in column A, other wise it
would
not.

Columns(1).SpecialCells(xlBlanks).EntireRow.Delete
--
Regards,
Tom Ogilvy

"Kaj Pedersen" wrote in message
...
Hi
Is it possible to write a macro that removes empty rows in a
specified
range?
I hope someone can help.
Regards
Kaj Pedersen

--
(ROT13)

Remove empty rows
 

David,

That delete routine is indeed very fast. I am considering changing the one
I use.
For what its worth...I thing you will find that an "EntireRow" is an entire
row, while "Rows" is contained within the specified range.
Also, have you tried this with Merged cells?

Regards,
Jim Cone
San Francisco, CA
****************
"David J. Braden" wrote in message
...
Ron,
Thanks for the promised test. As you know, I do this off of Mac/Unix, so
I can only hazard a guess how it will work out under Windows..
The other issue that raises its ugly head is "what is a blank cell"?
Idea (I learned from Myrna Larson) is to build up what you can, then do
the action to get the worksheet side of things to do the looping, as
that's generally much more efficient than looping with VBA through
worksheet stuff like this.
As you know, .Item(l).SpecialCells(xlCellTypeBlanks).EntireRow is the
entire row *within UsedRange* as opposed to having Excel check all 256
cells in a row.
Please post any improvements you can think of.
Also, I don't need one line I had, given the code structure, and had an
unneccesary Dim. So replace what I posted with:
Sub DeleteEmptyRows()
'Dave Braden
Dim l As Long, rng As Range, rngDel As Range
On Error Resume Next
Set rng = ActiveSheet.UsedRange
With rng.Columns
Set rngDel = .Item(1).SpecialCells(xlCellTypeBlanks).EntireRow
For l = 2 To .Count
Set rngDel = Intersect(rngDel, _
.Item(l).SpecialCells(xlCellTypeBlanks).EntireRow)
If rngDel Is Nothing Then Exit Sub
Next
End With
Application.ScreenUpdating = False
rngDel.Delete
End Sub
Could be there's a tradeoff as far as the sha[e of the worksheet; mine
almost always have far more rown than columns, so what I suggest will
likely be quite a lot faster, on average.
Regards,
Dave B

-snip-

Remove empty rows
 

Jim,
Under VBA 5.x on the Mac, (and I hope both VBA 5.x and 6.x on the
Windows versions), .EntireRow can be restricted to the entire row *of
the parent range*; as I wrote it, it does *not* go for the entire row
(256 cells) of the sheet, leaving less for Excell to work through .
Since the parent range is .UsedRange, it makes sense to me, even if
UsedRange is inaccurately bloated. You can see for yourself by stepping
through the routine up to the For loop, then checking out the address of
rngDel in the Immediate window: ?rngDel.Address.

I don't see how this could possibly fail with merged cells, given that
among them will be a cell address with data (I assume that's the issue
here), in which case the intersection rules that row out of the set to
be deleted. If I am off on this, *please* let me know.

Regards,
Dave Braden
MVP - Excel
< 30 minutes south of you <g

In article ,
"Jim Cone" wrote:

David,

That delete routine is indeed very fast. I am considering changing the one
I use.
For what its worth...I thing you will find that an "EntireRow" is an entire
row, while "Rows" is contained within the specified range.
Also, have you tried this with Merged cells?

Regards,
Jim Cone
San Francisco, CA
****************
"David J. Braden" wrote in message
...
Ron,
Thanks for the promised test. As you know, I do this off of Mac/Unix, so
I can only hazard a guess how it will work out under Windows..
The other issue that raises its ugly head is "what is a blank cell"?
Idea (I learned from Myrna Larson) is to build up what you can, then do
the action to get the worksheet side of things to do the looping, as
that's generally much more efficient than looping with VBA through
worksheet stuff like this.
As you know, .Item(l).SpecialCells(xlCellTypeBlanks).EntireRow is the
entire row *within UsedRange* as opposed to having Excel check all 256
cells in a row.
Please post any improvements you can think of.
Also, I don't need one line I had, given the code structure, and had an
unneccesary Dim. So replace what I posted with:
Sub DeleteEmptyRows()
'Dave Braden
Dim l As Long, rng As Range, rngDel As Range
On Error Resume Next
Set rng = ActiveSheet.UsedRange
With rng.Columns
Set rngDel = .Item(1).SpecialCells(xlCellTypeBlanks).EntireRow
For l = 2 To .Count
Set rngDel = Intersect(rngDel, _
.Item(l).SpecialCells(xlCellTypeBlanks).EntireRow)
If rngDel Is Nothing Then Exit Sub
Next
End With
Application.ScreenUpdating = False
rngDel.Delete
End Sub
Could be there's a tradeoff as far as the sha[e of the worksheet; mine
almost always have far more rown than columns, so what I suggest will
likely be quite a lot faster, on average.
Regards,
Dave B

-snip-



--
(ROT13)

Remove empty rows
 

Dave,

Entirerow refers to the 256 cells. specialcells is what restricts itself to
the used range. In your code, the net result is the same, but your
statement does not appear to be correct unless you meant something different
than what you appear to have said.

? range("A1:B3").EntireRow.Address
$1:$3
? range("A1:B3").SpecialCells(xlBlanks).EntireRow.Ad dress
$2:$2,$3:$3
? range("A1:B3").SpecialCells(xlBlanks).Address
$B$2,$A$3:$B$3
? range("1:3").SpecialCells(xlBlanks).Address
$C$1:$E$1,$B$2,$D$2:$F$3,$A$3:$B$3
? activesheet.UsedRange.Address
$A$1:$F$6

I suspect you would find the same in the MAC.

--
Regards,
Tom Ogilvy


David J. Braden wrote in message
...
Jim,
Under VBA 5.x on the Mac, (and I hope both VBA 5.x and 6.x on the
Windows versions), .EntireRow can be restricted to the entire row *of
the parent range*; as I wrote it, it does *not* go for the entire row
(256 cells) of the sheet, leaving less for Excell to work through .
Since the parent range is .UsedRange, it makes sense to me, even if
UsedRange is inaccurately bloated. You can see for yourself by stepping
through the routine up to the For loop, then checking out the address of
rngDel in the Immediate window: ?rngDel.Address.

I don't see how this could possibly fail with merged cells, given that
among them will be a cell address with data (I assume that's the issue
here), in which case the intersection rules that row out of the set to
be deleted. If I am off on this, *please* let me know.

Regards,
Dave Braden
MVP - Excel
< 30 minutes south of you <g

In article ,
"Jim Cone" wrote:

David,

That delete routine is indeed very fast. I am considering changing the
one
I use.
For what its worth...I thing you will find that an "EntireRow" is an
entire
row, while "Rows" is contained within the specified range.
Also, have you tried this with Merged cells?

Regards,
Jim Cone
San Francisco, CA
****************
"David J. Braden" wrote in message
...
Ron,
Thanks for the promised test. As you know, I do this off of Mac/Unix,
so
I can only hazard a guess how it will work out under Windows..
The other issue that raises its ugly head is "what is a blank cell"?
Idea (I learned from Myrna Larson) is to build up what you can, then
do
the action to get the worksheet side of things to do the looping, as
that's generally much more efficient than looping with VBA through
worksheet stuff like this.
As you know, .Item(l).SpecialCells(xlCellTypeBlanks).EntireRow is the
entire row *within UsedRange* as opposed to having Excel check all 256
cells in a row.
Please post any improvements you can think of.
Also, I don't need one line I had, given the code structure, and had
an
unneccesary Dim. So replace what I posted with:
Sub DeleteEmptyRows()
'Dave Braden
Dim l As Long, rng As Range, rngDel As Range
On Error Resume Next
Set rng = ActiveSheet.UsedRange
With rng.Columns
Set rngDel = .Item(1).SpecialCells(xlCellTypeBlanks).EntireRow
For l = 2 To .Count
Set rngDel = Intersect(rngDel, _
.Item(l).SpecialCells(xlCellTypeBlanks).EntireRow)
If rngDel Is Nothing Then Exit Sub
Next
End With
Application.ScreenUpdating = False
rngDel.Delete
End Sub
Could be there's a tradeoff as far as the sha[e of the worksheet; mine
almost always have far more rown than columns, so what I suggest will
likely be quite a lot faster, on average.
Regards,
Dave B

-snip-



--
(ROT13)

Remove empty rows
 

Tom,
I meant what I think you think I had meant to have meant. <g
Thanks for the clarification.
Now, see if you can speed this thing up for the "average" case.
TIA, and regards,
Dave B

In article ,
"Tom Ogilvy" wrote:

Dave,

Entirerow refers to the 256 cells. specialcells is what restricts itself to
the used range. In your code, the net result is the same, but your
statement does not appear to be correct unless you meant something different
than what you appear to have said.

? range("A1:B3").EntireRow.Address
$1:$3
? range("A1:B3").SpecialCells(xlBlanks).EntireRow.Ad dress
$2:$2,$3:$3
? range("A1:B3").SpecialCells(xlBlanks).Address
$B$2,$A$3:$B$3
? range("1:3").SpecialCells(xlBlanks).Address
$C$1:$E$1,$B$2,$D$2:$F$3,$A$3:$B$3
? activesheet.UsedRange.Address
$A$1:$F$6

I suspect you would find the same in the MAC.

--
Regards,
Tom Ogilvy


David J. Braden wrote in message
...
Jim,
Under VBA 5.x on the Mac, (and I hope both VBA 5.x and 6.x on the
Windows versions), .EntireRow can be restricted to the entire row *of
the parent range*; as I wrote it, it does *not* go for the entire row
(256 cells) of the sheet, leaving less for Excell to work through .
Since the parent range is .UsedRange, it makes sense to me, even if
UsedRange is inaccurately bloated. You can see for yourself by stepping
through the routine up to the For loop, then checking out the address of
rngDel in the Immediate window: ?rngDel.Address.

I don't see how this could possibly fail with merged cells, given that
among them will be a cell address with data (I assume that's the issue
here), in which case the intersection rules that row out of the set to
be deleted. If I am off on this, *please* let me know.

Regards,
Dave Braden
MVP - Excel
< 30 minutes south of you <g

In article ,
"Jim Cone" wrote:

David,

That delete routine is indeed very fast. I am considering changing the
one
I use.
For what its worth...I thing you will find that an "EntireRow" is an
entire
row, while "Rows" is contained within the specified range.
Also, have you tried this with Merged cells?

Regards,
Jim Cone
San Francisco, CA
****************
"David J. Braden" wrote in message
...
Ron,
Thanks for the promised test. As you know, I do this off of Mac/Unix,
so
I can only hazard a guess how it will work out under Windows..
The other issue that raises its ugly head is "what is a blank cell"?
Idea (I learned from Myrna Larson) is to build up what you can, then
do
the action to get the worksheet side of things to do the looping, as
that's generally much more efficient than looping with VBA through
worksheet stuff like this.
As you know, .Item(l).SpecialCells(xlCellTypeBlanks).EntireRow is the
entire row *within UsedRange* as opposed to having Excel check all 256
cells in a row.
Please post any improvements you can think of.
Also, I don't need one line I had, given the code structure, and had
an
unneccesary Dim. So replace what I posted with:
Sub DeleteEmptyRows()
'Dave Braden
Dim l As Long, rng As Range, rngDel As Range
On Error Resume Next
Set rng = ActiveSheet.UsedRange
With rng.Columns
Set rngDel = .Item(1).SpecialCells(xlCellTypeBlanks).EntireRow
For l = 2 To .Count
Set rngDel = Intersect(rngDel, _
.Item(l).SpecialCells(xlCellTypeBlanks).EntireRow)
If rngDel Is Nothing Then Exit Sub
Next
End With
Application.ScreenUpdating = False
rngDel.Delete
End Sub
Could be there's a tradeoff as far as the sha[e of the worksheet; mine
almost always have far more rown than columns, so what I suggest will
likely be quite a lot faster, on average.
Regards,
Dave B

-snip-



--
(ROT13)



--
(ROT13)

Remove empty rows
 

I set up a sheet with 5000 rows all blank except a value in the last column
(IV).
the last row, was filled. The only totally blank row was 4999

I modified ron's code to build a rng using union so deletion would occur in
one step. I didn't actually do the deletion, just built the range in both
routines.

I built a 1 to 10 loop to call the routine.
Time:
0.7734375 Ron (modified)
4.730469 Dave

I then copied the above for a total of 30000 rows (6 blank rows, 256
columns, 30K total rows)
Time:
8.019531 Ron (modified)
27.1875 Dave

I then delete AA:IV and put the former IV in AA (6 blank rows, 27 columns,
30K total rows)
Time:
4.511719 Ron (modified)
2.640625 Dave

I tried a couple of modifications to your code, but the modifications
weren't faster - about the same.

It appears that countA does take some cognizance of the usedrange.
The low number of blank rows was in Ron's favor - building the range would
probably incur a larger penalty for Ron's if more rows were blank.

If I select A2:A4000 and did clear contents to increase the number of rows
that were blank (~4000 vice 6):
Time: (~4000 blank rows, 27 columns, 30K rows)
5.488281 Ron (modified)
2.578125 Dave

Ron's time increased, but yours stays about the same.


Ron's modified code:

Sub DeleteEmptyRowsRon()
Dim rng As Range
'JW
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then 'Rows(R).Delete
If rng Is Nothing Then
Set rng = Rows(r)
Else
Set rng = Union(rng, Rows(r))
End If
End If
Next r
' rng.delete
End Sub

--
Regards,
Tom Ogilvy




David J. Braden wrote in message
...
Tom,
I meant what I think you think I had meant to have meant. <g
Thanks for the clarification.
Now, see if you can speed this thing up for the "average" case.
TIA, and regards,
Dave B

In article ,
"Tom Ogilvy" wrote:

Dave,

Entirerow refers to the 256 cells. specialcells is what restricts
itself to
the used range. In your code, the net result is the same, but your
statement does not appear to be correct unless you meant something
different
than what you appear to have said.

? range("A1:B3").EntireRow.Address
$1:$3
? range("A1:B3").SpecialCells(xlBlanks).EntireRow.Ad dress
$2:$2,$3:$3
? range("A1:B3").SpecialCells(xlBlanks).Address
$B$2,$A$3:$B$3
? range("1:3").SpecialCells(xlBlanks).Address
$C$1:$E$1,$B$2,$D$2:$F$3,$A$3:$B$3
? activesheet.UsedRange.Address
$A$1:$F$6

I suspect you would find the same in the MAC.

--
Regards,
Tom Ogilvy


David J. Braden wrote in message
...
Jim,
Under VBA 5.x on the Mac, (and I hope both VBA 5.x and 6.x on the
Windows versions), .EntireRow can be restricted to the entire row *of
the parent range*; as I wrote it, it does *not* go for the entire row
(256 cells) of the sheet, leaving less for Excell to work through .
Since the parent range is .UsedRange, it makes sense to me, even if
UsedRange is inaccurately bloated. You can see for yourself by
stepping
through the routine up to the For loop, then checking out the address
of
rngDel in the Immediate window: ?rngDel.Address.

I don't see how this could possibly fail with merged cells, given that
among them will be a cell address with data (I assume that's the issue
here), in which case the intersection rules that row out of the set to
be deleted. If I am off on this, *please* let me know.

Regards,
Dave Braden
MVP - Excel
< 30 minutes south of you <g

In article ,
"Jim Cone" wrote:

David,

That delete routine is indeed very fast. I am considering changing
the
one
I use.
For what its worth...I thing you will find that an "EntireRow" is an
entire
row, while "Rows" is contained within the specified range.
Also, have you tried this with Merged cells?

Regards,
Jim Cone
San Francisco, CA
****************
"David J. Braden" wrote in message
...
Ron,
Thanks for the promised test. As you know, I do this off of
Mac/Unix,
so
I can only hazard a guess how it will work out under Windows..
The other issue that raises its ugly head is "what is a blank
cell"?
Idea (I learned from Myrna Larson) is to build up what you can,
then
do
the action to get the worksheet side of things to do the looping,
as
that's generally much more efficient than looping with VBA through
worksheet stuff like this.
As you know, .Item(l).SpecialCells(xlCellTypeBlanks).EntireRow is
the
entire row *within UsedRange* as opposed to having Excel check all
256
cells in a row.
Please post any improvements you can think of.
Also, I don't need one line I had, given the code structure, and
had
an
unneccesary Dim. So replace what I posted with:
Sub DeleteEmptyRows()
'Dave Braden
Dim l As Long, rng As Range, rngDel As Range
On Error Resume Next
Set rng = ActiveSheet.UsedRange
With rng.Columns
Set rngDel = .Item(1).SpecialCells(xlCellTypeBlanks).EntireRow
For l = 2 To .Count
Set rngDel = Intersect(rngDel, _
.Item(l).SpecialCells(xlCellTypeBlanks).EntireRow)
If rngDel Is Nothing Then Exit Sub
Next
End With
Application.ScreenUpdating = False
rngDel.Delete
End Sub
Could be there's a tradeoff as far as the sha[e of the worksheet;
mine
almost always have far more rown than columns, so what I suggest
will
likely be quite a lot faster, on average.
Regards,
Dave B

-snip-



--
(ROT13)



--
(ROT13)

Remove empty rows
 

Hi David

I see that Tom did some great testing
I can beat the master with that<g

Ron


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



"David J. Braden" wrote in message ...
Ron,
Thanks for the promised test. As you know, I do this off of Mac/Unix, so
I can only hazard a guess how it will work out under Windows..

The other issue that raises its ugly head is "what is a blank cell"?

Idea (I learned from Myrna Larson) is to build up what you can, then do
the action to get the worksheet side of things to do the looping, as
that's generally much more efficient than looping with VBA through
worksheet stuff like this.

As you know, .Item(l).SpecialCells(xlCellTypeBlanks).EntireRow is the
entire row *within UsedRange* as opposed to having Excel check all 256
cells in a row.

Please post any improvements you can think of.

Also, I don't need one line I had, given the code structure, and had an
unneccesary Dim. So replace what I posted with:

Sub DeleteEmptyRows()
'Dave Braden
Dim l As Long, rng As Range, rngDel As Range

On Error Resume Next
Set rng = ActiveSheet.UsedRange
With rng.Columns
Set rngDel = .Item(1).SpecialCells(xlCellTypeBlanks).EntireRow

For l = 2 To .Count
Set rngDel = Intersect(rngDel, _
.Item(l).SpecialCells(xlCellTypeBlanks).EntireRow)
If rngDel Is Nothing Then Exit Sub
Next
End With
Application.ScreenUpdating = False
rngDel.Delete
End Sub

Could be there's a tradeoff as far as the sha[e of the worksheet; mine
almost always have far more rown than columns, so what I suggest will
likely be quite a lot faster, on average.

Regards,
Dave B

In article ,
"Ron de Bruin" wrote:

Hi David
I have save the sub to test it this weekend.
It looks good
Thanks for posting it
--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl

"David J. Braden" wrote in message
...
Ron,
This appears substantially faster than John's and Chip's approaches,
from test data I've devised. Can't say it's always faster.

Sub DeleteEmptyRows()
'Dave Braden
Dim l As Long, rng As Range, rngCol As Range, rngDel As Range

On Error Resume Next
Set rng = ActiveSheet.UsedRange
With rng.Columns
Set rngDel = .Item(1).SpecialCells(xlCellTypeBlanks).EntireRow
If rngDel Is Nothing Then Exit Sub
For l = 2 To .Count
Set rngDel = Intersect(rngDel, _
.Item(l).SpecialCells(xlCellTypeBlanks).EntireRow)
If rngDel Is Nothing Then Exit Sub
Next
End With
rngDel.Delete
End Sub

In article ,
"Ron de Bruin" wrote:

Or testing the whole row

Sub DeleteEmptyRows()
'JW
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For R = LastRow To 1 Step -1
If Application.CountA(Rows(R)) = 0 Then Rows(R).Delete
Next R
End Sub
--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl


"Tom Ogilvy" wrote in message
...
assume an empty row would have a blank cell in column A, other wise it
would
not.

Columns(1).SpecialCells(xlBlanks).EntireRow.Delete
--
Regards,
Tom Ogilvy

"Kaj Pedersen" wrote in message
...
Hi
Is it possible to write a macro that removes empty rows in a
specified
range?
I hope someone can help.
Regards
Kaj Pedersen

--
(ROT13)

Remove empty rows
 

Thanks Tom for your time to test this

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



"Tom Ogilvy" wrote in message ...
I set up a sheet with 5000 rows all blank except a value in the last column
(IV).
the last row, was filled. The only totally blank row was 4999

I modified ron's code to build a rng using union so deletion would occur in
one step. I didn't actually do the deletion, just built the range in both
routines.

I built a 1 to 10 loop to call the routine.
Time:
0.7734375 Ron (modified)
4.730469 Dave

I then copied the above for a total of 30000 rows (6 blank rows, 256
columns, 30K total rows)
Time:
8.019531 Ron (modified)
27.1875 Dave

I then delete AA:IV and put the former IV in AA (6 blank rows, 27 columns,
30K total rows)
Time:
4.511719 Ron (modified)
2.640625 Dave

I tried a couple of modifications to your code, but the modifications
weren't faster - about the same.

It appears that countA does take some cognizance of the usedrange.
The low number of blank rows was in Ron's favor - building the range would
probably incur a larger penalty for Ron's if more rows were blank.

If I select A2:A4000 and did clear contents to increase the number of rows
that were blank (~4000 vice 6):
Time: (~4000 blank rows, 27 columns, 30K rows)
5.488281 Ron (modified)
2.578125 Dave

Ron's time increased, but yours stays about the same.


Ron's modified code:

Sub DeleteEmptyRowsRon()
Dim rng As Range
'JW
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then 'Rows(R).Delete
If rng Is Nothing Then
Set rng = Rows(r)
Else
Set rng = Union(rng, Rows(r))
End If
End If
Next r
' rng.delete
End Sub

--
Regards,
Tom Ogilvy




David J. Braden wrote in message
...
Tom,
I meant what I think you think I had meant to have meant. <g
Thanks for the clarification.
Now, see if you can speed this thing up for the "average" case.
TIA, and regards,
Dave B

In article ,
"Tom Ogilvy" wrote:

Dave,

Entirerow refers to the 256 cells. specialcells is what restricts
itself to
the used range. In your code, the net result is the same, but your
statement does not appear to be correct unless you meant something
different
than what you appear to have said.

? range("A1:B3").EntireRow.Address
$1:$3
? range("A1:B3").SpecialCells(xlBlanks).EntireRow.Ad dress
$2:$2,$3:$3
? range("A1:B3").SpecialCells(xlBlanks).Address
$B$2,$A$3:$B$3
? range("1:3").SpecialCells(xlBlanks).Address
$C$1:$E$1,$B$2,$D$2:$F$3,$A$3:$B$3
? activesheet.UsedRange.Address
$A$1:$F$6

I suspect you would find the same in the MAC.

--
Regards,
Tom Ogilvy


David J. Braden wrote in message
...
Jim,
Under VBA 5.x on the Mac, (and I hope both VBA 5.x and 6.x on the
Windows versions), .EntireRow can be restricted to the entire row *of
the parent range*; as I wrote it, it does *not* go for the entire row
(256 cells) of the sheet, leaving less for Excell to work through .
Since the parent range is .UsedRange, it makes sense to me, even if
UsedRange is inaccurately bloated. You can see for yourself by
stepping
through the routine up to the For loop, then checking out the address
of
rngDel in the Immediate window: ?rngDel.Address.

I don't see how this could possibly fail with merged cells, given that
among them will be a cell address with data (I assume that's the issue
here), in which case the intersection rules that row out of the set to
be deleted. If I am off on this, *please* let me know.

Regards,
Dave Braden
MVP - Excel
< 30 minutes south of you <g

In article ,
"Jim Cone" wrote:

David,

That delete routine is indeed very fast. I am considering changing
the
one
I use.
For what its worth...I thing you will find that an "EntireRow" is an
entire
row, while "Rows" is contained within the specified range.
Also, have you tried this with Merged cells?

Regards,
Jim Cone
San Francisco, CA
****************
"David J. Braden" wrote in message
...
Ron,
Thanks for the promised test. As you know, I do this off of
Mac/Unix,
so
I can only hazard a guess how it will work out under Windows..
The other issue that raises its ugly head is "what is a blank
cell"?
Idea (I learned from Myrna Larson) is to build up what you can,
then
do
the action to get the worksheet side of things to do the looping,
as
that's generally much more efficient than looping with VBA through
worksheet stuff like this.
As you know, .Item(l).SpecialCells(xlCellTypeBlanks).EntireRow is
the
entire row *within UsedRange* as opposed to having Excel check all
256
cells in a row.
Please post any improvements you can think of.
Also, I don't need one line I had, given the code structure, and
had
an
unneccesary Dim. So replace what I posted with:
Sub DeleteEmptyRows()
'Dave Braden
Dim l As Long, rng As Range, rngDel As Range
On Error Resume Next
Set rng = ActiveSheet.UsedRange
With rng.Columns
Set rngDel = .Item(1).SpecialCells(xlCellTypeBlanks).EntireRow
For l = 2 To .Count
Set rngDel = Intersect(rngDel, _
.Item(l).SpecialCells(xlCellTypeBlanks).EntireRow)
If rngDel Is Nothing Then Exit Sub
Next
End With
Application.ScreenUpdating = False
rngDel.Delete
End Sub
Could be there's a tradeoff as far as the sha[e of the worksheet;
mine
almost always have far more rown than columns, so what I suggest
will
likely be quite a lot faster, on average.
Regards,
Dave B

-snip-



--
(ROT13)



--
(ROT13)

Remove empty rows
 

Can't I mean

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



"Ron de Bruin" wrote in message ...
Hi David

I see that Tom did some great testing
I can beat the master with that<g

Ron


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



"David J. Braden" wrote in message ...
Ron,
Thanks for the promised test. As you know, I do this off of Mac/Unix, so
I can only hazard a guess how it will work out under Windows..

The other issue that raises its ugly head is "what is a blank cell"?

Idea (I learned from Myrna Larson) is to build up what you can, then do
the action to get the worksheet side of things to do the looping, as
that's generally much more efficient than looping with VBA through
worksheet stuff like this.

As you know, .Item(l).SpecialCells(xlCellTypeBlanks).EntireRow is the
entire row *within UsedRange* as opposed to having Excel check all 256
cells in a row.

Please post any improvements you can think of.

Also, I don't need one line I had, given the code structure, and had an
unneccesary Dim. So replace what I posted with:

Sub DeleteEmptyRows()
'Dave Braden
Dim l As Long, rng As Range, rngDel As Range

On Error Resume Next
Set rng = ActiveSheet.UsedRange
With rng.Columns
Set rngDel = .Item(1).SpecialCells(xlCellTypeBlanks).EntireRow

For l = 2 To .Count
Set rngDel = Intersect(rngDel, _
.Item(l).SpecialCells(xlCellTypeBlanks).EntireRow)
If rngDel Is Nothing Then Exit Sub
Next
End With
Application.ScreenUpdating = False
rngDel.Delete
End Sub

Could be there's a tradeoff as far as the sha[e of the worksheet; mine
almost always have far more rown than columns, so what I suggest will
likely be quite a lot faster, on average.

Regards,
Dave B

In article ,
"Ron de Bruin" wrote:

Hi David
I have save the sub to test it this weekend.
It looks good
Thanks for posting it
--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl

"David J. Braden" wrote in message
...
Ron,
This appears substantially faster than John's and Chip's approaches,
from test data I've devised. Can't say it's always faster.

Sub DeleteEmptyRows()
'Dave Braden
Dim l As Long, rng As Range, rngCol As Range, rngDel As Range

On Error Resume Next
Set rng = ActiveSheet.UsedRange
With rng.Columns
Set rngDel = .Item(1).SpecialCells(xlCellTypeBlanks).EntireRow
If rngDel Is Nothing Then Exit Sub
For l = 2 To .Count
Set rngDel = Intersect(rngDel, _
.Item(l).SpecialCells(xlCellTypeBlanks).EntireRow)
If rngDel Is Nothing Then Exit Sub
Next
End With
rngDel.Delete
End Sub

In article ,
"Ron de Bruin" wrote:

Or testing the whole row

Sub DeleteEmptyRows()
'JW
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For R = LastRow To 1 Step -1
If Application.CountA(Rows(R)) = 0 Then Rows(R).Delete
Next R
End Sub
--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl


"Tom Ogilvy" wrote in message
...
assume an empty row would have a blank cell in column A, other wise it
would
not.

Columns(1).SpecialCells(xlBlanks).EntireRow.Delete
--
Regards,
Tom Ogilvy

"Kaj Pedersen" wrote in message
...
Hi
Is it possible to write a macro that removes empty rows in a
specified
range?
I hope someone can help.
Regards
Kaj Pedersen

--
(ROT13)

Remove empty rows
 

Tom,
Thanks.
Modifying Ron's./ohn's code to build up the region before the delete is
great.

More in-line:

In article ,
"Tom Ogilvy" wrote:

I set up a sheet with 5000 rows all blank except a value in the last column
(IV).
the last row, was filled. The only totally blank row was 4999

I modified ron's code to build a rng using union so deletion would occur in
one step. I didn't actually do the deletion, just built the range in both
routines.

I built a 1 to 10 loop to call the routine.
Time:
0.7734375 Ron (modified)
4.730469 Dave

Interesting. Yet the probability of having such a worksheet arising
naturally out of an honest, super-moronic modelling effort is far lower
than having G.W. Bush putting on a red sequined gown for the next State
of the Union address.

I then copied the above for a total of 30000 rows (6 blank rows, 256
columns, 30K total rows)
Time:
8.019531 Ron (modified)
27.1875 Dave

Weird. OK, now I have to include the probability of a sequined gown of
any color. <g

I then delete AA:IV and put the former IV in AA (6 blank rows, 27 columns,
30K total rows)
Time:
4.511719 Ron (modified)
2.640625 Dave

I tried a couple of modifications to your code, but the modifications
weren't faster - about the same.

It appears that countA does take some cognizance of the usedrange.
The low number of blank rows was in Ron's favor - building the range would
probably incur a larger penalty for Ron's if more rows were blank.

If I select A2:A4000 and did clear contents to increase the number of rows
that were blank (~4000 vice 6):
Time: (~4000 blank rows, 27 columns, 30K rows)
5.488281 Ron (modified)
2.578125 Dave

Ron's time increased, but yours stays about the same.

So for regular stuff, it appears my suggestion is about twice as fast?
That is faster (a bit) than what I got with my tests, but how to
determine the "average" situation eludes me. If you figure out a
heuristic that one might invoke to call either of the two routines,
could you let me know? Driving the routines with a parameter (e.g.,
bGW_InDrag) could use the best of both.

Hmmmm, come to think of it, I think I have it... will repost shortly if
it works.

Regards,
Dave B
MVP - Excel



Ron's modified code:

Sub DeleteEmptyRowsRon()
Dim rng As Range
'JW
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then 'Rows(R).Delete
If rng Is Nothing Then
Set rng = Rows(r)
Else
Set rng = Union(rng, Rows(r))
End If
End If
Next r
' rng.delete
End Sub


<<snip

****************
"David J. Braden" wrote in message
...
Ron,
Thanks for the promised test. As you know, I do this off of
Mac/Unix,
so
I can only hazard a guess how it will work out under Windows..
The other issue that raises its ugly head is "what is a blank
cell"?
Idea (I learned from Myrna Larson) is to build up what you can,
then
do
the action to get the worksheet side of things to do the looping,
as
that's generally much more efficient than looping with VBA through
worksheet stuff like this.
As you know, .Item(l).SpecialCells(xlCellTypeBlanks).EntireRow is
the
entire row *within UsedRange* as opposed to having Excel check all
256
cells in a row.
Please post any improvements you can think of.
Also, I don't need one line I had, given the code structure, and
had
an
unneccesary Dim. So replace what I posted with:
Sub DeleteEmptyRows()
'Dave Braden
Dim l As Long, rng As Range, rngDel As Range
On Error Resume Next
Set rng = ActiveSheet.UsedRange
With rng.Columns
Set rngDel = .Item(1).SpecialCells(xlCellTypeBlanks).EntireRow
For l = 2 To .Count
Set rngDel = Intersect(rngDel, _
.Item(l).SpecialCells(xlCellTypeBlanks).EntireRow)
If rngDel Is Nothing Then Exit Sub
Next
End With
Application.ScreenUpdating = False
rngDel.Delete
End Sub
Could be there's a tradeoff as far as the sha[e of the worksheet;
mine
almost always have far more rown than columns, so what I suggest
will
likely be quite a lot faster, on average.
Regards,

--
(ROT13)