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

 

[musicman715]

Will check out Excel Jeanie when I get a chance...so how do I get to the cell below my las Non Blank cell...I.E. the first blank cell in column A?

To me, from the other posts, it looks like I should use a rows.count and xlup command...if this is true, can you, or someone explain the logic behind that, much like you just did with the xldown command?...that was very helpful
Thanks,
Musicman715

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.

 

[Joe4]

so how do I get to the cell below my las Non Blank cell...I.E. the first blank cell in column A?

That is what the formulas Smitty and I gave you from the get-go do. Did you even try them?

To me, from the other posts, it looks like I should use a rows.count and xlup command...if this is true, can you, or someone explain the logic behind that, much like you just did with the xldown command?...that was very helpful

Did you read either of my two previous replies?

 

[Jonmo1]

Will check out Excel Jeanie when I get a chance...so how do I get to the cell below my las Non Blank cell...I.E. the first blank cell in column A?

To me, from the other posts, it looks like I should use a rows.count and xlup command...if this is true, can you, or someone explain the logic behind that, much like you just did with the xldown command?...that was very helpful
Thanks,
Musicman715

That's the purpose of the offset

Cells(Rows.Count,"A").End(xlup) get's you the last non blank cell in column A.
Then offset(1,0) get's you 1 row down from that.

 

[musicman715]

Thanks Joe4...So to rephrase what you said...rows.count takes me to the absolute bottom of column A...then xlup takes me from the bottom up to the last populated cell in column A, and offset (1,0) takes me 1 cell below that....thanks a million...I have yet to try it, but I'm confident this should work, and once I put it in my code, I'll let everybody know the result.

Thanks Everyone!

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

 

[Joe4]

Thanks Joe4...So to rephrase what you said...rows.count takes me to the absolute bottom of column A...then xlup takes me from the bottom up to the last populated cell in column A, and offset (1,0) takes me 1 cell below that.

Exactly.

 

[musicman715]

Hey Everybody...Works beautifully!
Thanks,
Musicman715

Thanks Joe4...So to rephrase what you said...rows.count takes me to the absolute bottom of column A...then xlup takes me from the bottom up to the last populated cell in column A, and offset (1,0) takes me 1 cell below that.

Exactly.