End(XlUp) For Last Used Row in Column

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

  • 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

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

  • 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 :)

  • 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
  • Re: End(XlUp) For Last Used Row in Column


    Maybe this will help too...



    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!