twist on next empty cell
 

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.

--
David

twist on next empty cell
 

How about:
Cells(33, 1).End(xlUp).Offset(1, 0).Select

RBS

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

--
David

twist on next empty cell
 

RB Smissaert wrote

How about:
Cells(33, 1).End(xlUp).Offset(1, 0).Select


Thanks, that addresses part of the problem, but that only gets to the first
empty cell in column A, and I could have used xlDown to accomplish that.

Main issue remains:
If, for example, column D has more entries than column A, I want to select
the cell in column A one row below the number of entries in column D.

--
David

twist on next empty cell
 

OK, then do:
Cells(33, 4).End(xlUp).Offset(1, -3).Select

RBS

"David" wrote in message
...
RB Smissaert wrote

How about:
Cells(33, 1).End(xlUp).Offset(1, 0).Select


Thanks, that addresses part of the problem, but that only gets to the
first
empty cell in column A, and I could have used xlDown to accomplish that.

Main issue remains:
If, for example, column D has more entries than column A, I want to select
the cell in column A one row below the number of entries in column D.

--
David

twist on next empty cell
 

RB Smissaert wrote

OK, then do:
Cells(33, 4).End(xlUp).Offset(1, -3).Select


OK, now that looks at Column D, but I may not have explained myself well
enough. I want the line to look at the one that has the most entries. That
will vary.

--
David

twist on next empty cell
 

OK, then do:

Sub FindNextRow()

Dim c As Byte
Dim r As Long
Dim LR As Long
Dim LRMax As Long

For c = 1 To 5
LR = Cells(33, c).End(xlUp).Row
If LR LRMax Then
LRMax = LR
End If
Next

Cells(LRMax + 1, 1).Select

End Sub

Change the 5 according to the numbers of columns you have to look at.


RBS


"David" wrote in message
...
RB Smissaert wrote

OK, then do:
Cells(33, 4).End(xlUp).Offset(1, -3).Select


OK, now that looks at Column D, but I may not have explained myself well
enough. I want the line to look at the one that has the most entries. That
will vary.

--
David

twist on next empty cell
 

RB Smissaert wrote

OK, then do:

Sub FindNextRow()

Dim c As Byte
Dim r As Long
Dim LR As Long
Dim LRMax As Long

For c = 1 To 5
LR = Cells(33, c).End(xlUp).Row
If LR LRMax Then
LRMax = LR
End If
Next

Cells(LRMax + 1, 1).Select

End Sub

Change the 5 according to the numbers of columns you have to look at.

I was trying, as stated in my original post, for a single line solution,
but if there's no way, then I guess I'll have to settle for another sub :(

--
David

twist on next empty cell
 

I can't see a simple one-line solution, but maybe one of the real Excel
experts could tell you better.

RBS


"David" wrote in message
...
RB Smissaert wrote

OK, then do:

Sub FindNextRow()

Dim c As Byte
Dim r As Long
Dim LR As Long
Dim LRMax As Long

For c = 1 To 5
LR = Cells(33, c).End(xlUp).Row
If LR LRMax Then
LRMax = LR
End If
Next

Cells(LRMax + 1, 1).Select

End Sub

Change the 5 according to the numbers of columns you have to look at.

I was trying, as stated in my original post, for a single line solution,
but if there's no way, then I guess I'll have to settle for another sub :(

--
David

twist on next empty cell
 

try this-- activecell.offset(rowoffset:=1,columnoffset:=-4).activate


"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.

--
David

twist on next empty cell
 

?B?RE1vbmV5?= wrote

try this-- activecell.offset(rowoffset:=1,columnoffset:=-4).activate

Thanks for jumping in. My needs dictate that the active cell not move to a
new location.

--
David

twist on next empty cell
 

David,

The following line returns the last row with data on the spreadsheet.
Add 1 to get the next row...

BottomRow = ActiveSheet.Cells.Find(what:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

Regards,
Jim Cone
San Francisco, CA


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

twist on next empty cell
 

Jim Cone wrote

BottomRow = ActiveSheet.Cells.Find(what:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row


Couldn't get past Application Error 1004 trying to incorporate this.

Here's what I've settled on, lacking further input from you or others:

Moved Averaging formulas to row 2

Sub GetStats()
For i = 1 To 6
With Workbooks("attendance stats.xls").Sheets(i)
..Range("B5:F5").Copy Range("A" & ActiveSheet.UsedRange.Rows.Count + 1)
..Range("G5:K5").Copy Range("A" & ActiveSheet.UsedRange.Rows.Count + 1)
..Range("L5:P5").Copy Range("A" & ActiveSheet.UsedRange.Rows.Count + 1)
..Range("Q5:U5").Copy Range("A" & ActiveSheet.UsedRange.Rows.Count + 1)
..Range("V5:Z5").Copy Range("A" & ActiveSheet.UsedRange.Rows.Count + 1)
End With
Next
End Sub

What it does is grabs weekday attendance totals from source workbook for
the past six months. I just have to close the workbook without saving
before I can run it again -- unless there's a way to reset UsedRange to 2
rows.

--
David

twist on next empty cell
 

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.

twist on next empty cell
 

Your code assumes that the lowest filled cell in the 5 columns is in column E
and that cell happens to be active. I don't think the OP indicated that either
of those were pre-existing conditions.

On Sun, 21 Nov 2004 09:53:05 -0800, DMoney
wrote:

try this-- activecell.offset(rowoffset:=1,columnoffset:=-4).activate


"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.

--
David

twist on next empty cell
 

Myrna Larson wrote

I tested the code below with 37 cells filled in the range, the
lowest one E19.

Thanks for looking at this. To further complicate things, the copied ranges
frequently contain blanks, so the range in any given column after looping
through the ranges from 6 sheets of the source file may not be full. That's
why my current routine adjusts for UsedRange.

Sub GetStats()
For i = 1 To 6
With Workbooks("attendance stats.xls").Sheets(i)
..Range("B5:F5").Copy Range("A" & ActiveSheet.UsedRange.Rows.Count + 1)
..Range("G5:K5").Copy Range("A" & ActiveSheet.UsedRange.Rows.Count + 1)
..Range("L5:P5").Copy Range("A" & ActiveSheet.UsedRange.Rows.Count + 1)
..Range("Q5:U5").Copy Range("A" & ActiveSheet.UsedRange.Rows.Count + 1)
..Range("V5:Z5").Copy Range("A" & ActiveSheet.UsedRange.Rows.Count + 1)
End With
Next
End Sub

--
David