finding first empty cell

[vrzimmerm]

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.

 

[JW]

Sub foo()
Sheets("Master").Cells(Cells(Rows.Count, 1) _
.End(xlUp).Row, 1).Offset(1, 0) _
.PasteSpecial xlPasteAll
End Sub

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

Tom, wouldn't you also need to qualify "Rows.Count"? If the activesheet is a
chart your code would error as well.

 

[vrzimmerm]

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.

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.

 

[Guest]

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.

 

[JW]

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.

 

[Guest]

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.