Results 1 to 4 of 4
  1. #1
    viccaji
    Guest

    Identify any 'corner' cell address of selection (MS-Excel 2000 (9.0.3821 S

    Hi

    This is a VBA-related question

    Does anyone know how to select / identify the cell address of any of the 'corner' cells of a selected range ?

    For example, if I have defined the cell-range 'A2:F25' as a named-range ('Database'), and I need to select the cell A26 (the cell below the bottom-left corner of the range), how do I do this.

    I've tried umpteen combinations of the .Offset(R1, C1).Select method, but can't seem to get anywhere.
    I've also tried cruder variants as a workaround :

    Variant - 1
    Range("A65536").End(xlUp).Select
    But this presumes that no cells below the 'Database' range are occupied !

    Variant - 2
    Range("A2").End(xlDown).Offset(1,0).Select
    This one presumes that no blank cells exist in the range A2:A25 !

    PLEASE HELP...

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Identify any 'corner' cell address of selection (MS-Excel 2000 (9.0.38

    Hi,
    There are probably neater ways to do this but something like:
    ActiveSheet.Cells(Range("test").Row + Range("test").Rows.Count, 1).Select
    would select the cell in column A directly below range 'test'.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Identify any 'corner' cell address of selection (MS-Excel 2000 (9.0.38

    The following will select the first cell in first column beneath the range Database.<pre> Dim rngData As Range
    Set rngData = Range("Database")
    rngData.Cells(1).Offset(rngData.Rows.Count, 0).Select</pre>

    which could also be coded as :<pre> [Database].Cells(1).Offset([Database].Rows.Count, 0).Select</pre>


    Andrew C

  4. #4
    viccaji
    Guest

    Re: Identify any 'corner' cell address of selection (MS-Excel 2000 (9.0.38

    Hi Andrew --

    Thanks a lot.
    Just what I needed.
    In fact, I checked out the keyword 'Cells' in VBA Help after posting my query... I thought that would be the way to approach it...
    Thanks once again

    Khushnood Viccaji

Posting Permissions

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