Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
range, cell refrence, but with a new twist | Excel Worksheet Functions | |||
Cell protection - with a twist | Excel Discussion (Misc queries) | |||
why a reference to an empty cell is not considered empty | Excel Discussion (Misc queries) | |||
Splitting a cell, with a twist! | Excel Discussion (Misc queries) | |||
Empty cell and a the empty String | Excel Programming |