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)