Explanation of Code in VBA

[seasmith]

Range("A65536").End(xlUp)(1, 2) = txtTitle.Text 'Prints the text in the Title column'


This code goes to a text box on a form and it prints whatever I type in the text box directly into the first blank row in the second column. I found this code on this site, but I would like to understand it better as to what it is actually doing especially what the .End(xlUp) part is? If you have any knowledge about this it would be much appreciated. Thank you ahead of time

 

[wigi]

You "go" to the last cell in column A, which was row 65536 in Excel 2003 and some version before. Now it's a lot more.

The End(xlUp) is the equivalent of Ctrl-Arrow up to find the next cell in the same column that is not empty.

The (1,2) then offsets to the correct cell in column B, one row below.

 

[Jonmo1]

The end(xlUp) is simulating pressing CTRL + UP on your keyboard.
So it's as if you went to A65536, then pressed CTRL + UP
It finds the last occupied cell in column A.

Then the (1,2) after that is almost like an offset..
More like index.

From whatever cell the end(xlup) found (say it was A25)
It then Indexes from there 1 row and 2 columns (INCLUDING A25).
So 1 row from A25 (including A25) is A25
2 Columns from A25(including A25) is B25

so it ends up with B25


Hope that helps.

 

[seasmith]

Thanks! That was a great explanation. Just curious, is this a common way used to find the next empty row?

 

[Jonmo1]

Thanks! That was a great explanation. Just curious, is this a common way used to find the next empty row?

Yes, it's very common.
But it does NOT find the next empty row...
It actually finds the LAST NON EMPTY row...
There's a big difference.

Standby for more..

 

[seasmith]

If it finds the last non empty row why is it putting whatever I type in the text box in the row after this which is the next empty row? Does it have something to do with the offset?

 

[Jonmo1]

Range("A65536").End(xlUp)(1, 2)

The red part finds the last non empty cell in column A
the blue part then goes from that cell 1 down and 2 right (INCLUDING the previous cell)

 

[seasmith]

Thanks so much this really helped me understand it.

 

[Jonmo1]

Just one last note...

Instead of Range("A65536")
It's wise to use Range("A" & Rows.Count)

Because Different versions of Excel have different amount of Rows available.
XL2003 has 65536
XL2007 and above have 1 Million +

The purpose of the 65536 was to look at the end of the sheet, then go up.
This may fail in other versions if they are using more than that many rows.
Rows.Count will always = the amount of rows available, regardless of XL version.

 

[seasmith]

Thanks that is good to know too since I am using 2010