Range("A1").End(xldown) question

[musicman715]

Hi All,
I'm trying to use the following code in my macro to copy and paste info from an invoice, into another Excel 2010 Invoice Data Sheet:
Range("A1").End(xldown).Offset(1,0).select
When I run that piece of code using the F8 line by line function Excel gives me an error '1004' Application-defined or 'object-defined Run Time Error. I pressed help to try to solve it, but not much is helpful there.
Any help will be appreciated Thanks, Musicman715

 

[Smitty]

It's most likely failing because there's nothing in column A, so the code goes to the last cell and is trying to go one cell below that, which is impossilble.

You're better off using xlUp anyway:

Cells(Rows.Count, "A").End(xlUp).Copy

HTH,

FYI - I deleted your duplicate post.

 

[Joe4]

Is there any data in column A past row 1 to start with?
If not, Range("A1").End(xldown) will take you down to your last available row on your worksheet, but then the Offset(1,0) will try to take you down one more row, which is impossible if you are on the last possible row already.

Perhaps this version will work better (it works from the bottom up rather than the top down)?
Cells(Rows.Count,"A").End(xlUp).Offset(1,0).Select

 

[musicman715]

Thanks for deleting that duplicate...I didn't realize it was out there...I'll try this xlup when I get a chance...why is it better?

It's most likely failing because there's nothing in column A, so the code goes to the last cell and is trying to go one cell below that, which is impossilble.

You're better off using xlUp anyway:

Cells(Rows.Count, "A").End(xlUp).Copy

HTH,

FYI - I deleted your duplicate post.

 

[Joe4]

why is it better?

Look at the first pargaraph in my previous post.

The only caveat to this method is if there is absolutely no data anywhere in column A, it will start on row 2, not row 1. If that is a real possibility that needs to be accounted for, that can be addressed with a little more code.

 

[musicman715]

First, Cell A1 is my column heading...beyond that it is blank.
Second,...so Rang("A1").End(xldown) takes me to the first blank cell in column A already? Is this correct?....In this case, is it true, then, that if that's where I want to be, I don't need the offset(1,0) at all?

Is there any data in column A past row 1 to start with?
If not, Range("A1").End(xldown) will take you down to your last available row on your worksheet, but then the Offset(1,0) will try to take you down one more row, which is impossible if you are on the last possible row already.

Perhaps this version will work better (it works from the bottom up rather than the top down)?
Cells(Rows.Count,"A").End(xlUp).Offset(1,0).Select

 

[Jonmo1]

Second,...so Rang("A1").End(xldown) takes me to the first blank cell in column A already? Is this correct?

No..

xldown takes you to the last NON blank cell prior to the next blank cell.
Or if you are already on the last non blank prior to a blank, then it goes to the next non blank after the blanks.
If there are no more non blanks, then it goes to the last row.

xldown just simulates pressing CTRL + DOWN on your keyboard.
If you want a true understanding of how it works..
Put some data in a column, and just randomly blank some of them out.
Make sure to leave A1 NOT blank.
Then start in A1 and just press CTRL + DOWN while observing the results.

 

[musicman715]

As it currently stands, Column heading in A1, nothing below that, but as I run the macro when it works correctly, the macro will copy the invoice # into A2, then A3 etc...if I use Rows.count, then exl up, currently with all cells blank below A1, won't that command count A1 as my first row as that cell does have text in it?, then try to move up from A1? And as I add more info in A2, A3 etc. won't that command move to the last non-blank cell in column A, then move up from there? Or am I incorrect about this?

Look at the first pargaraph in my previous post.

The only caveat to this method is if there is absolutely no data anywhere in column A, it will start on row 2, not row 1. If that is a real possibility that needs to be accounted for, that can be addressed with a little more code.

 

[Joe4]

First, Cell A1 is my column heading...beyond that it is blank.

Then the solutiona that Smitty and I proposed would be perfect for you.

Second,...so Rang("A1").End(xldown) takes me to the first blank cell in column A already? Is this correct?....In this case, is it true, then, that if that's where I want to be, I don't need the offset(1,0) at all?

That is not what it does. It take you to the last populate cell in the contiguous range. I.e, if you have data in every cell A1:A10, it takes you down to cell A10. If you have no data after A1, it takes you to the last possible row on the spreadsheet. See the problem?

To see exactly how this works, "Range("A1").End(xlDown)" is the same as placing your cursor in cell A1 and pressing "CTRL-Down Arrow". Notice the behavior. Try it out will different cells populated in column A to see what it does. It is looking for the last populated cell in the data range in that column (if you have cells A1:A4 populated, and A5 blank, then A6 populated, it will stop at A4 when starting in A1).

 

[Joe4]

As it currently stands, Column heading in A1, nothing below that, but as I run the macro when it works correctly, the macro will copy the invoice # into A2, then A3 etc...if I use Rows.count, then exl up, currently with all cells blank below A1, won't that command count A1 as my first row as that cell does have text in it?, then try to move up from A1? And as I add more info in A2, A3 etc. won't that command move to the last non-blank cell in column A, then move up from there? Or am I incorrect about this?

It works just the opposite of xlDown, like I described in my previous post.

xlUp will find the LAST populated cell in column A.
Then the Offset(1,0) command will move one row below that.

All that Rows.Count does is start at the very bottom on your speadsheet, in the last possible row (NOT in the last populated row). This allows the code to work for any version of Excel (i.e. Excel 2003 allows 65536 rows, Excel 2007 allows 1048576).