Dynamic Range in a macro

deuxo · Apr 17th, 2002, 05:34 PM

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 · Apr 17th, 2002, 08:34 PM

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 · Apr 17th, 2002, 08:43 PM

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 · Apr 17th, 2002, 10:02 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 17th, 2002, 05:34 PM	#1
deuxo New Member Join Date: Mar 2002 Posts: 13	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.

Apr 17th, 2002, 08:34 PM	#2
SamS Board Regular Join Date: Feb 2002 Location: Brisbane, Down Under Posts: 533	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

Apr 17th, 2002, 08:43 PM	#3
anno Board Regular Join Date: Feb 2002 Posts: 202	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 ]

Apr 17th, 2002, 10:02 PM	#4
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	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