Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding first empty cell
I am attempting to paste some data into the first non-blank row below
a matrix of data. I'm using the following coding to do this (which I have used before): Sheets("Master").Select Range("A1").Select Selection.End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Now, I keep getting a run time error on the selection line. What am I doing wrong? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding first empty cell
Sub foo()
Sheets("Master").Cells(Cells(Rows.Count, 1) _ .End(xlUp).Row, 1).Offset(1, 0) _ .PasteSpecial xlPasteAll End Sub wrote: I am attempting to paste some data into the first non-blank row below a matrix of data. I'm using the following coding to do this (which I have used before): Sheets("Master").Select Range("A1").Select Selection.End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Now, I keep getting a run time error on the selection line. What am I doing wrong? Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding first empty cell
I believe that means that the active cell is the last row, so when you do
..End(xlDown) you get row 65536. You then try to offset that by one row which returns the Runtime error. Try working from the bottom up: With Worksheets("Master") .Range("A" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteAll End With -- Charles Chickering "A good example is twice the value of good advice." " wrote: I am attempting to paste some data into the first non-blank row below a matrix of data. I'm using the following coding to do this (which I have used before): Sheets("Master").Select Range("A1").Select Selection.End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Now, I keep getting a run time error on the selection line. What am I doing wrong? Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding first empty cell
The code will return an error if column A is blank or only the first row has
data. Try going from bottom to top. Sheets("Master").Select Range("A65536").End(xlUp).Offset(1, 0).Select ActiveSheet.Paste -- Hope that helps. Vergel Adriano " wrote: I am attempting to paste some data into the first non-blank row below a matrix of data. I'm using the following coding to do this (which I have used before): Sheets("Master").Select Range("A1").Select Selection.End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Now, I keep getting a run time error on the selection line. What am I doing wrong? Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding first empty cell
You would have that problem if it went all the way to the bottom of the
worksheet. Try coming up from the bottom. set r= worksheets("Master").cells(rows.count,1).End(xlup) (2) selection.copy r also, avoid selecting and your code will run much faster. -- Regards, Tom Ogilvy " wrote: I am attempting to paste some data into the first non-blank row below a matrix of data. I'm using the following coding to do this (which I have used before): Sheets("Master").Select Range("A1").Select Selection.End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Now, I keep getting a run time error on the selection line. What am I doing wrong? Thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding first empty cell
Why do you only qualify one instance of Cells. If Master is the active sheet,
you don't need to qualify any (unless the code is in a sheet module other than master) If it isn't then you need to qualify both: Sub foo() With Sheets("Master") .Cells(.Cells(Rows.Count, 1) _ .End(xlUp).Row, 1).Offset(1, 0) _ .PasteSpecial xlPasteAll End with End Sub or just use sheets("Master").Cells(rows.count,1).end( _ xlup).offset(1,0).Pastespecial xlPasteall Just a thought that may eliminate some potential error/code failure situations. -- Regards, Tom Ogilvy "JW" wrote: Sub foo() Sheets("Master").Cells(Cells(Rows.Count, 1) _ .End(xlUp).Row, 1).Offset(1, 0) _ .PasteSpecial xlPasteAll End Sub wrote: I am attempting to paste some data into the first non-blank row below a matrix of data. I'm using the following coding to do this (which I have used before): Sheets("Master").Select Range("A1").Select Selection.End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Now, I keep getting a run time error on the selection line. What am I doing wrong? Thanks. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding first empty cell
Tom, wouldn't you also need to qualify "Rows.Count"? If the activesheet is a
chart your code would error as well. -- Charles Chickering "A good example is twice the value of good advice." "Tom Ogilvy" wrote: Why do you only qualify one instance of Cells. If Master is the active sheet, you don't need to qualify any (unless the code is in a sheet module other than master) If it isn't then you need to qualify both: Sub foo() With Sheets("Master") .Cells(.Cells(Rows.Count, 1) _ .End(xlUp).Row, 1).Offset(1, 0) _ .PasteSpecial xlPasteAll End with End Sub or just use sheets("Master").Cells(rows.count,1).end( _ xlup).offset(1,0).Pastespecial xlPasteall Just a thought that may eliminate some potential error/code failure situations. -- Regards, Tom Ogilvy "JW" wrote: Sub foo() Sheets("Master").Cells(Cells(Rows.Count, 1) _ .End(xlUp).Row, 1).Offset(1, 0) _ .PasteSpecial xlPasteAll End Sub wrote: I am attempting to paste some data into the first non-blank row below a matrix of data. I'm using the following coding to do this (which I have used before): Sheets("Master").Select Range("A1").Select Selection.End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Now, I keep getting a run time error on the selection line. What am I doing wrong? Thanks. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding first empty cell
On Sep 19, 8:48 am, Tom Ogilvy
wrote: Why do you only qualify one instance of Cells. If Master is the active sheet, you don't need to qualify any (unless the code is in a sheet module other than master) If it isn't then you need to qualify both: Sub foo() With Sheets("Master") .Cells(.Cells(Rows.Count, 1) _ .End(xlUp).Row, 1).Offset(1, 0) _ .PasteSpecial xlPasteAll End with End Sub or just use sheets("Master").Cells(rows.count,1).end( _ xlup).offset(1,0).Pastespecial xlPasteall Just a thought that may eliminate some potential error/code failure situations. -- Regards, Tom Ogilvy "JW" wrote: Sub foo() Sheets("Master").Cells(Cells(Rows.Count, 1) _ .End(xlUp).Row, 1).Offset(1, 0) _ .PasteSpecial xlPasteAll End Sub wrote: I am attempting to paste some data into the first non-blank row below a matrix of data. I'm using the following coding to do this (which I have used before): Sheets("Master").Select Range("A1").Select Selection.End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Now, I keep getting a run time error on the selection line. What am I doing wrong? Thanks. Thanks all. I didn't realize that when my matrix only has data in the header row the coding would go all the way to the bottom of the sheet then try to offset it by 1 more row. I'll start at the bottom and work up. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding first empty cell
Certainly a valid consideration, but no, I have never had a chart as the
active sheet when I am copying to another worksheet and trying to paste a value in the next available row - doesn't make much sense, but if it is a problem for you, than by all means. It never hurts to code defensively. In contrast the problem I highlight is extremely prevelant and often hard to debug. -- regards, Tom Ogilvy "Charles Chickering" wrote: Tom, wouldn't you also need to qualify "Rows.Count"? If the activesheet is a chart your code would error as well. -- Charles Chickering "A good example is twice the value of good advice." "Tom Ogilvy" wrote: Why do you only qualify one instance of Cells. If Master is the active sheet, you don't need to qualify any (unless the code is in a sheet module other than master) If it isn't then you need to qualify both: Sub foo() With Sheets("Master") .Cells(.Cells(Rows.Count, 1) _ .End(xlUp).Row, 1).Offset(1, 0) _ .PasteSpecial xlPasteAll End with End Sub or just use sheets("Master").Cells(rows.count,1).end( _ xlup).offset(1,0).Pastespecial xlPasteall Just a thought that may eliminate some potential error/code failure situations. -- Regards, Tom Ogilvy "JW" wrote: Sub foo() Sheets("Master").Cells(Cells(Rows.Count, 1) _ .End(xlUp).Row, 1).Offset(1, 0) _ .PasteSpecial xlPasteAll End Sub wrote: I am attempting to paste some data into the first non-blank row below a matrix of data. I'm using the following coding to do this (which I have used before): Sheets("Master").Select Range("A1").Select Selection.End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Now, I keep getting a run time error on the selection line. What am I doing wrong? Thanks. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding first empty cell
Just a simple typo on my part. As for qualifying the sheet, I like to
play it safe. The code might not have been in a sheet module at all. It may have been in a standard module and could therefore potentially be ran on any number of sheets within the workbook. Tom Ogilvy wrote: Why do you only qualify one instance of Cells. If Master is the active sheet, you don't need to qualify any (unless the code is in a sheet module other than master) If it isn't then you need to qualify both: Sub foo() With Sheets("Master") .Cells(.Cells(Rows.Count, 1) _ .End(xlUp).Row, 1).Offset(1, 0) _ .PasteSpecial xlPasteAll End with End Sub or just use sheets("Master").Cells(rows.count,1).end( _ xlup).offset(1,0).Pastespecial xlPasteall Just a thought that may eliminate some potential error/code failure situations. -- Regards, Tom Ogilvy "JW" wrote: Sub foo() Sheets("Master").Cells(Cells(Rows.Count, 1) _ .End(xlUp).Row, 1).Offset(1, 0) _ .PasteSpecial xlPasteAll End Sub wrote: I am attempting to paste some data into the first non-blank row below a matrix of data. I'm using the following coding to do this (which I have used before): Sheets("Master").Select Range("A1").Select Selection.End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Now, I keep getting a run time error on the selection line. What am I doing wrong? Thanks. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding first empty cell
Sorry if I stepped on your toes. I was trying to help the OP avoid having
to figure out a transient and hard to debug problem. -- Regards, Tom Ogilvy "JW" wrote: Just a simple typo on my part. As for qualifying the sheet, I like to play it safe. The code might not have been in a sheet module at all. It may have been in a standard module and could therefore potentially be ran on any number of sheets within the workbook. Tom Ogilvy wrote: Why do you only qualify one instance of Cells. If Master is the active sheet, you don't need to qualify any (unless the code is in a sheet module other than master) If it isn't then you need to qualify both: Sub foo() With Sheets("Master") .Cells(.Cells(Rows.Count, 1) _ .End(xlUp).Row, 1).Offset(1, 0) _ .PasteSpecial xlPasteAll End with End Sub or just use sheets("Master").Cells(rows.count,1).end( _ xlup).offset(1,0).Pastespecial xlPasteall Just a thought that may eliminate some potential error/code failure situations. -- Regards, Tom Ogilvy "JW" wrote: Sub foo() Sheets("Master").Cells(Cells(Rows.Count, 1) _ .End(xlUp).Row, 1).Offset(1, 0) _ .PasteSpecial xlPasteAll End Sub wrote: I am attempting to paste some data into the first non-blank row below a matrix of data. I'm using the following coding to do this (which I have used before): Sheets("Master").Select Range("A1").Select Selection.End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Now, I keep getting a run time error on the selection line. What am I doing wrong? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding First Empty Cell in a Column | Excel Discussion (Misc queries) | |||
Finding next empty empty cell in a range of columns | Excel Programming | |||
Finding next available empty cell in a row | Excel Programming | |||
Finding the first empty cell in a column | Excel Programming | |||
Finding the next empty cell. | Excel Programming |