End(XlUp) For Last Used Row in Column

Joozh · May 16th 2007

Hi,

I'm fighting to understand VBA; Boy is it fun or what :)...

[FONT="System"][COLOR="Blue"]BTW, I sense that if I have a solid understanding of the Excel object model, learning and using VBA would become much easier. Is this correct? Can you kindly share some good URLs which can help? Hope you'll help and guide.[/COLOR][/FONT]

Now coming to the question:

I have copied a piece of code and am trying to understand how it work. The scenario is that the following values are in Column A (starting with cell A1) i.e. A1 contains the value 81 and A5 contains the value 124:

81
44
713
997
124

I can't understanding the working of the following line of code:

Code

iRow = Worksheets("Sheet1").Cells(Rows.Count,1).End(XlUp).Row

Here's my understanding:

The focus is moved to the last row of Column #1 in Sheet1. This is achieved by
".Cells(Rows.Count,1)"
Then the focus moves up to the first cell that contains any value. This is achieved by
".End(XlUp).Row"

I was expecting that after execution of the above code, iRow it will hold the value 124 (which are the contents of the 1st row that is encountered when the code executes ".End(XlUp).Row"). However the debug window shows that iRow contains the value 5??? Seems like that contrary to my understanding, iRow is holding a pointer to A5??

I hope this is not a (too) naive post and advance thanks not only for your reply but more importantly for guiding me in the right direction which will help me get very comfortable using VBA.

**Reafidy** · May 16th 2007

Re: Does The Code Return The Cell Value Or A Pointer To The Cell?

The code you are using doesnt change the focus of the cell.

Basically what it does is returns the row no containing the last used cell in the column.
Row number 5.

because its the 5th line in:

81
44
713
997
124

This will shift the focus to the last used cell in the column

Code

Worksheets("Sheet1").Cells(Rows.Count,1).End(XlUp).select

This will return the value of the last used cell:

Code

irowvalue = Worksheets("Sheet1").Cells(Rows.Count,1).End(XlUp).value

HTH

Joozh · May 16th 2007

Re: Does The Code Return The Cell Value Or A Pointer To The Cell?

Thanks Reafidy.

This helps and now I'm one step closer to understanding & using VBA Still thousands of more steps to go...

Joozh · May 16th 2007

Re: End(XlUp) For Last Used Row in Column

Hi,

Another question from the same exercise...

After execution of the following code, Rng1 contains the value 81. Now this time, I was expecting the row # of the first cell in column 1 (i.e. A1).

Code

Sub Test()
Dim Rng1 As Range
...


Set Rng1 = Worksheets("Sheet1").Cells(1,1)


...
End Sub

Thanks for putting up with my questions

**Reafidy** · May 16th 2007

Re: End(XlUp) For Last Used Row in Column

Im not sure I follow correctly but what you are doing there is setting a range to Rng1.

you could retreive the value with:

Code

debug.print Rng1.value
'which is the same as
debug.print Worksheets("Sheet1").Cells(1,1) .value

**Ger Plante** · May 16th 2007

Re: End(XlUp) For Last Used Row in Column

Maybe this will help too...

Code

Public Sub test()


Dim rng1 As Range


Set rng1 = Worksheets(1).Cells(1, 1)


MsgBox rng1 'value stored in the cell
MsgBox rng1.Value 'value stored in the cell
MsgBox rng1.Address 'Address that rng1 refers to


End Sub

Display More

Ger

End(XlUp) For Last Used Row in Column

Participate now!

Share