You can start your End(xlUp) statement from row 33 instead of from row 65536.

I don't believe there is any single-line solution for this. If the range
A2:E32 contains some cell with constants and no formulas, in theory you could
reduce it to a couple of lines rather than a loop. But there seems to be a bug
in Excel. I tested the code below with 37 cells filled in the range, the
lowest one E19. The last line evaluates to Rng.Cells(52).Row. The code returns
52 instead of 19.

Sub LastRow()
Dim Rng As Range
Dim N As Long

Set Rng = Range("A1:E32").SpecialCells(xlCellTypeConstants)
N = Rng.Cells.Count
Debug.Print Rng.Address, N, Rng.Cells(N).Address, Rng.Cells(N).Row
End Sub

The result is:

$A$1:$A$3,$D$1:$D$3,$B$1:$B$5,$C$1:$C$7,$E$1:$E$19 37 $A$37 37

Rng.Cells(N) is evaluated as cell #37 in the *FIRST AREA* of Rng. The 1st area
is A1:A3, but under-the-hood it is expanded to include 37 cells, A1:A37.

So the code would have to check the row number of the last cell in each area
in turn, or just loop through the columns as you are now doing.


On Sun, 21 Nov 2004 05:48:24 -0800, David wrote:

I've seen hundreds of ways to activate the next empty cell in a column, but
I need to activate a cell in column A one row down from the last entry in a
range of cells ("A2:E32") to copy more data into.

The problems I've run into stem from the fact that cells A33:E33 contain
averages of the data in that range, so they are included in any xlUP or
xlDown or UsedRange arguments.

What I want is a single statement as opposed to a cell scan.