Dynamic Range in a macro

[deuxo]

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.

 

[SamS]

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

 

[anno]

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

 

[Jay Petrulis]

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