Results 1 to 7 of 7

Thread: Top left cell

  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello. (Excel 2003)
    Is there a property that will tell me the top left cell of the current workbook or window?
    A shape has a top left cell, and I wondered if a sheet did as well. Thanks, Andy.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The top left cell of a sheet is A1, obviously.

    The top left cell of the currently visible range is ActiveWindow.VisibleRange.Cells(1,1)

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks.

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    To clarify slightly what Hans has given you;

    Activewindow.VisibleRange.Cells(1,1) returns the value of the cell
    Activewindow.VisibleRange.Cells(1,1).Address returns the address
    Regards
    Don

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    To clarify slightly what Don has given you:

    Activewindow.VisibleRange.Cells(1,1) returns the cell as a Range object, but depending on how you use it, you get the object or its default property, i.e. its value

    Example 1:

    Dim rngCell As Range
    Set rngCell = Activewindow.VisibleRange.Cells(1,1)

    This assigns the top left cell as a Range object to rngCell. You can retrieve rngCell.Address or rngCell.Value etc.

    Example 2:

    Dim strValue As String
    strValue = Activewindow.VisibleRange.Cells(1,1)

    Because you can't assign an object to a string, VBA automatically assigns the default property of the Range object, i.e. the Value property, to strValue. The line

    strValue = Activewindow.VisibleRange.Cells(1,1)

    is equivalent to

    strValue = Activewindow.VisibleRange.Cells(1,1).Value

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by HansV View Post
    Because you can't assign an object to a string, VBA automatically assigns the default property of the Range object, i.e. the Value property, to strValue. The line

    strValue = Activewindow.VisibleRange.Cells(1,1)
    Thank you Hans

    I intuitively expected that to return an error.
    Regards
    Don

  7. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    Central Texas, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    No, it does not, which is probably unfortunate, since I've had to debug code where variables did not have the expected object because there was no "set".

    --Scott.

Posting Permissions

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