Remove empty rows

  • Thread starter Thread starter Kaj Pedersen
  • Start date Start date
K

Kaj Pedersen

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

Regards
Kaj Pedersen
 
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
 
assume an empty row would have a blank cell in column A, other wise it would
not.

Columns(1).SpecialCells(xlBlanks).EntireRow.Delete
 
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
 
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
 
Hi David

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

Thanks for posting it
 
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
 
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 said:
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
Click to expand...

-snip-
 
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>

Jim Cone said:
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 said:
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
Click to expand...

-snip-
Click to expand...
 
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.Address
$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 said:
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>

Jim Cone said:
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 said:
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
Click to expand...

-snip-
Click to expand...
Click to expand...
 
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

Tom Ogilvy said:
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.Address
$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 said:
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>

Jim Cone said:
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
****************
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-
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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

Tom Ogilvy said:
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.Address
$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 said:
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>

David,

That delete routine is indeed very fast. I am considering changing
Click to expand...
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
****************
Ron,
Thanks for the promised test. As you know, I do this off of
Click to expand...
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,
Click to expand...
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
Click to expand...
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-
Click to expand...
Click to expand...
Click to expand...
 
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)




David J. Braden said:
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
Click to expand...
 
Thanks Tom for your time to test this

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




Tom Ogilvy said:
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 said:
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

Tom Ogilvy said:
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.Address
$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


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>

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
****************
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-
Click to expand...
Click to expand...
Click to expand...
 
Can't I mean

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




Ron de Bruin said:
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)




David J. Braden said:
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

Ron de Bruin said:
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)


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

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
Click to expand...
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

Hi
Is it possible to write a macro that removes empty rows in a
specified
range?
I hope someone can help.
Regards
Kaj Pedersen
Click to expand...
Click to expand...
Click to expand...
 
Tom,
Thanks.
Modifying Ron's./ohn's code to build up the region before the delete is
great.

More in-line:

Tom Ogilvy said:
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
Click to expand...

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
Click to expand...

Weird. OK, now I have to include the probability of a sequined gown of
any color. said:
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.
Click to expand...

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

****************
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,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top