Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How do I get my Ranged area to excapand ? (Excel 97)

    I use a macro to select all the data I retrieve and each time the list is longer. How can I use the following macro to select a ranged area and sort it. If my ranged area changes each time I run this macro how can I get it to expand automaticly.

    Range("A6:F52").Select
    Selection.Sort Key1:=Range("A6"), Order1:=xlDescending, Key2:=Range("D6") _
    , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom

  2. #2
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I get my Ranged area to excapand ? (Excel 97)

    Instead of 'Range("A6:F52").Select'

    Try Using

    Range("A6").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

    This selects everything on a sheet - assuming you want everything

  3. #3
    New Lounger
    Join Date
    Sep 2001
    Location
    Paignton, Devon, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I get my Ranged area to excapand ? (Excel 97)

    If your range has no spaces you can use Range("A6").CurrentRegion.Select. Also, if you give the range a name then depending on how your data is refreshed it will expand automatically.

    HTH

    Graeme

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I get my Ranged area to excapand ? (Excel 97)

    Thank you... both of them worked. I used the first one as it was the easiest.

Posting Permissions

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