Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Dynamic Range in a macro

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

    Default

    I am looking for an examlple of a macro or the function I should use that will sort rows and columns of information in a worksheet where the number of rows or columns or both change daily. Thanks.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This post might help although there was a post in the last 12 months (on the old mesage board) that specifically dealt with your issue but you will need to do a search for it.

    http://mrexcel.com/wwwboard/messages/22511.html


  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    deuxo
    you could also check out dave hawley's site at http://www.ozgrid.com. the vba page has a bit on dynamic ranges. think it's called 'dynamic range via vba'. doesn't solve the whole problem though - doesn't include anything about sorting data.

    [ This Message was edited by: anno on 2002-04-17 19:43 ]

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Not sure I follow what you are trying to do. Here is a routine which cycles through each column of the active sheet and sorts each ascending.

    This treats all columns independent of one another, meaning it will *not* sort the expanded selection (like the choice that pops up when Excel tries to figure out what to sort).

    If you need to sort on more than 3 columns with grouped data, then this is not the technique to do so.

    No error checking is present, and the macro assumes row 1 houses the category headers. That can be changed if needed.

    '---begin VBA---
    Sub test()
    Dim lastcol As Integer
    Dim x As Integer, currlast As Long

    With ActiveSheet
    .UsedRange
    lastcol = .Cells.SpecialCells(xlCellTypeLastCell).Column

    For x = 1 To lastcol
    currlast = .Cells(Rows.Count, x).End(xlUp).Row
    .Range(.Cells(1, x), .Cells(currlast, x)).Sort _
    Key1:=.Cells(2, x), Order1:=xlAscending, Header:=xlYes
    Next x

    End With

    End Sub
    '---end VBA---

    HTH,
    Jay

Some videos you may like

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
  •