Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Virginia
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding Data to A Range (Excel97)

    I have an Excel Spreadsheet with names and addresses. When I want to sort it I have to redefine the range. When I add a new name to the list how can I automatically add it to the range to be sorted without having to redefine the range?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Adding Data to A Range (Excel97)

    If you only select 1 cell, sort will expand to select the whole "current region"

    Also in a macro you can select a cell and use the CurrentRegion property to select it:
    <pre>range("a1").currentregion.Select</pre>


    If you want a name that automatically expands you can define a name using offset:
    insert- name - define
    CountColA
    Refers to:
    =counta(Sheet1!$A:$A)
    Then a name:
    DataTable
    Refers to:
    =OFFSET(Sheet1!$A$1,0,0,CountColA,5)
    This example will define a range starting in A1:Ex
    Where x = the number of items in col A.

    Then you can use
    <pre>Range("DataTable").select</pre>

    in a macro and it will always be live

    Or you can use:
    <pre>range(range("a1"),range("E65536").End(xlUp)). Select</pre>

    to select from A1 to the last value in Col E.

    Steve

  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: Adding Data to A Range (Excel97)

    If the columns you store your names and addresses in have no other data, you can just use the entire columns. The following approach should work in VBA code if your names and addresses are stored in columns A to E, and there is no other data in those columns :

    Range("A:E").Sort Key1:= 'etc etc...


    Andrew C

Posting Permissions

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