Macro Noob i want to understand a code i got from this forum

[kimoishappy]

Hello everyone,

I'm a mega noob a Macros and i want to learn, i had a question how to paste stuff in the next availbale free space not over writting so i googled found your forum which i now love and added to favorites.

I saw this response and just modified the values for my use and it works like a charm but im the kinda guy that well just cause it works i dont let it be. i want to understand how it works can some one please drill it down for me?

Sub test1()
Range("A3:B16").Copy Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub


So the rage i get, the copy sheets i get.. this is what i get:
Range("A3:B16").Copy Sheets("Sheet4").Range("A"

what ever is after it i dont get... so this is the part i dont get:
& Rows.Count).End(xlUp).Offset(1, 0)

what does rows.count or the rest do? can some one please explain? also end(xlup)? offset?

please, thanks to this forum and its smart people i used the code and it works but now i want to understand it..

PS: Any good books you might recommend for a noob?

thank you

 

[lrobbo314]

Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Rows.Count returns a number that is the total number of rows in your version of Excel. So, 2010 and up, 1048576.
So it starts out as Range("A1048576")
the .End means it's going to move to the next non- blank cell. xlUp, is telling it to go up. There's xldown, xltoleft, and xltoright.
.Offset takes 2 arguments. .Offest(# of Rows, #of Columns)
So, .Offset(1,0) means, go down 1 row.

So, all that to say that it goes to the end, finds the first non-blank cell above it, then moves down 1 so that you are in the first available blank cell.

 

[Sektor]

Range("A" & Rows.Count).End(xlUp) is a common way to find out the last cell in some column (A in this case).
So, the code first calculates the number of the last row: Rows.Count. Some people write the exact number: 65536 (for Excel <=2003) or 1048576 (for Excel 2007+), but this code does it on your behalf. So, if we're in Excel 2007 it will be equal to Range("A1048576").
Then, the End method acts the same way as Ctrl+UpArrow shortcut key, hence we find ourselves in last filled cell in that colum (say, it will be cell A100).
The Offset(1, 0) merely offsets this cell down one row - hence, we get cell A101.
I hope this little explanation will clear picture a bit.

 

[kimoishappy]

Wow.. thank you for the time and explaining this to me makes sense..

Thank you

 

[kimoishappy]

THank you all for the quick reply i get it.. any good books you might recommend?

Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Rows.Count returns a number that is the total number of rows in your version of Excel. So, 2010 and up, 1048576.
So it starts out as Range("A1048576")
the .End means it's going to move to the next non- blank cell. xlUp, is telling it to go up. There's xldown, xltoleft, and xltoright.
.Offset takes 2 arguments. .Offest(# of Rows, #of Columns)
So, .Offset(1,0) means, go down 1 row.

So, all that to say that it goes to the end, finds the first non-blank cell above it, then moves down 1 so that you are in the first available blank cell.

Thank

 

[kimoishappy]

Thank you for the explanation, it does make sense.. wow.. i will practice more.. any good books you might recommend?

Thanks a million my friend..

Range("A" & Rows.Count).End(xlUp) is a common way to find out the last cell in some column (A in this case).
So, the code first calculates the number of the last row: Rows.Count. Some people write the exact number: 65536 (for Excel <=2003) or 1048576 (for Excel 2007+), but this code does it on your behalf. So, if we're in Excel 2007 it will be equal to Range("A1048576").
Then, the End method acts the same way as Ctrl+UpArrow shortcut key, hence we find ourselves in last filled cell in that colum (say, it will be cell A100).
The Offset(1, 0) merely offsets this cell down one row - hence, we get cell A101.
I hope this little explanation will clear picture a bit.

 

[Sektor]

The best book, I think, is Excel 2007 VBA Programmer's Reference. This book covers LOTS of topics so it's must have.

 

[kimoishappy]

Nice...

Thank you for this, will look in to it..

The best book, I think, is Excel 2007 VBA Programmer's Reference. This book covers LOTS of topics so it's must have.

 

[Sektor]