Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

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

  1. #1
    Join Date
    14th May 2003
    Posts
    405

    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:

    VB:
    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. #2
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,913

    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

    VB:
    Worksheets("Sheet1").Cells(Rows.Count,1).End(XlUp).select 
    
    
    This will return the value of the last used cell:

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

  3. #3
    Join Date
    14th May 2003
    Posts
    405

    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

  4. #4
    Join Date
    14th May 2003
    Posts
    405

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

    VB:
    Sub Test() 
        Dim Rng1 As Range 
        ... 
         
        Set Rng1 = Worksheets("Sheet1").Cells(1,1) 
         
        ... 
    End Sub 
    
    
    Thanks for putting up with my questions
    Thanks

    - J

  5. #5
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,913

    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:

    VB:
    Debug.Print Rng1.value 
     'which is the same as
    Debug.Print Worksheets("Sheet1").Cells(1,1) .value 
    
    

  6. #6
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,491

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

    Maybe this will help too...

    VB:
    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
    _______________________________________________
    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

    _______________________________________________

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Force End(xlup).row) To Use Column B?
    By kwl in forum EXCEL HELP
    Replies: 7
    Last Post: February 16th, 2007, 04:56
  2. End(xlUp).Row Not Finding Last Row/Cell
    By HuX in forum EXCEL HELP
    Replies: 3
    Last Post: August 30th, 2006, 15:44
  3. End(xlUp) vs SpecialCells(xlCellTypeLastCell)
    By ShosMeister in forum EXCEL HELP
    Replies: 5
    Last Post: August 8th, 2006, 19:41
  4. (xlUp)
    By booger in forum EXCEL HELP
    Replies: 3
    Last Post: March 6th, 2006, 13:22
  5. can't use end(xlup) from word
    By tommykuong in forum Excel and/or Word Help
    Replies: 1
    Last Post: October 29th, 2004, 17:01

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno