+ Reply to Thread
Results 1 to 5 of 5

Macro Sort column(s) with range that is continuously changing or being added to

  1. #1
    Registered User
    Join Date
    01-31-2011
    Location
    NYC
    MS-Off Ver
    Excel 2007
    Posts
    7

    Macro Sort column(s) with range that is continuously changing or being added to

    I created a Macro button that sorts columns specifically to print out at a report. This works great until I insert/add rows to the columns i am sorting: the range the macro was set up for does not match the # of rows in the column.

    Example:
    Orginal macro sorting range is: ActiveSheet.Range("$A$3:$V$100") where row 101 is blank, and row 102 is a subtotal. I add 2 rows, now I want my sorting range to be: ActiveSheet.Range("$A$3:$V$102") where row 103 is blank, and row 104 is a subtotal. This does not automatically update as I wrote the macro from the "Developer" tab "record macro" function, the range stays: ActiveSheet.Range("$A$3:$V$100").

    How does one have excel automatically update the range?
    Last edited by russell.suereth; 08-14-2012 at 12:02 PM.

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Macro Sort column(s) with range that is continuously changing or being added to

    Try this maybe

    Please Login or Register  to view this content.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Registered User
    Join Date
    01-31-2011
    Location
    NYC
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Macro Sort column(s) with range that is continuously changing or being added to

    Mike, can you explain how this works? I do not understand how the cells(rowindex, columnindex) returned the range.

  4. #4
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Macro Sort column(s) with range that is continuously changing or being added to

    Does it work like you want it to?
    The cells(rowindex, columnindex) looks at Row 3 Column A and .End(xlDown).Row works like if you Manual click in cell A3 then hold down the CTRL key and the press the down arrow key. It will goto the last cell in column A with data as long as there are no empty cells in between.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-31-2011
    Location
    NYC
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Macro Sort column(s) with range that is continuously changing or being added to

    Ok, understood. And yes, it worked perfectly. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1