Macro to define current active cell as part of a range after
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Macro to define current active cell as part of a range after

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I am trying to write a macro to select I particular range that is below two lists. So the First part of the Macro is this:

    Range("A1").Select

    ‘Bring me to end of 1st list
    Selection.End(xlDown).Select

    ‘Bring me to start of 2nd list
    Selection.End(xlDown).Select

    ‘Bring me to end of 2nd list
    Selection.End(xlDown).Select

    ‘Bring me to start of 3rd list
    Selection.End(xlDown).Select

    ‘Select Upper-Left Corner of List
    ActiveCell.Select

    ??? – How can I name the active cell as UpperLeft?

    UpperLeft = ActiveCell???

    Range("UpperLeft", Range("B65536").End(xlUp).Address).Select

    I don’t know how to get that ??? part? Is it Dim UpperLeft As Range?String?Cell?

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this code:

    UpperLeft = ActiveCell.Address
    Range(UpperLeft, Range("B65536").End(xlUp).Address).Select

    Hope it helps.
    Kind regards, Al.

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Dim UpperLeft As Range
    Set UpperLeft = ActiveCell
    Range(UpperLeft, [B65536].End(xlUp)).Select

    Your whole code could be re-written as follows (to avoid all that selecting) :-

    Dim UpperLeft As Range
    Set UpperLeft = [A1].End(xlDown).End(xlDown).End(xlDown).End(xlDown)
    Range(UpperLeft, [B65536].End(xlUp)).Select


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com