Announcement

Collapse
No announcement yet.

End(XlUp) For Last Used Row in Column

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • End(XlUp) For Last Used Row in Column

    Hi,

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

    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.

    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:
    1. The focus is moved to the last row of Column #1 in Sheet1. This is achieved by
      ".Cells(Rows.Count,1)"
    2. 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.
    Thanks

    - J

  • #2
    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
    Reafidy

    Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

    Comment


    • #3
      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...
      Thanks

      - J

      Comment


      • #4
        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
        Thanks

        - J

        Comment


        • #5
          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
          Reafidy

          Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

          Comment


          • #6
            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
            Ger

            Check out our new reputation system. Click on the "star" under the post!
            _______________________________________________

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

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

            The BEST Lookup function of all time

            Dynamic Named Ranges are your bestest friend

            _______________________________________________

            Comment

            Working...
            X