Utilizing a macro for a datasets that don't have a fixed number of

  • Thread starter Thread starter DyingIsis
  • Start date Start date
D

DyingIsis

Hello -

I have multiple datasets of varying numbers of rows, and I would like to
develop a macro that I can use for all of them.

Below is a sample table.
Item Type Rate 1 Rate 2
Item 1 0.025% 0.700%
Item 2 0.030% 0.700%
Item 3 0.030% 0.600%
Item 4 0.020% 0.500%

I would like the macro to rank "rate 2" first then rank "rate 1". Sometimes
the dataset will have 100's of records, sometimes the dataset will have
1,000's of records. The columns always stays the same.

Any help would greatly be appreciated.

Thanks for your time.
 
If by 'rank' you mean sort, then try the macro below. If that's not what you
meant, please explain a little more about what you expect the results to be.

To put the code into your workbook, open it and press [Alt]+[F11] to open
the VB Editor (VBE). In the VBE, choose Insert --> Module and copy the code
and paste it into the code module presented to you. Then make any
modifications to the column identifiers I've defined using the Const
statement.

After that, simply choose the sheet you want to sort the data on and use
Tools --> Macro --> Macros and choose the macro in the list and click the
[Run] button (Excel 2003 and earlier). In Excel 2007 you run the macro from
the "Developer" ribbon.

Sub SortByRate2ThenRate1()
'change these Const values
'as required for your sheet's setup
Const firstColToSort = "A"
Const lastColToSort = "C"
'first column to base sort on
'as your Rate1 column
Const firstKeyCol = "C"
'2nd column to base sort on
'as your Rate2 column
Const secondKeyCol = "B"

Dim sortRange As Range
Dim sKey1 As Range
Dim sKey2 As Range
Dim lastRow As Long

'find out how far down the sheet
'the list to be sorted goes
lastRow = _
ActiveSheet.Range(firstColToSort & _
Rows.Count).End(xlUp).Row
'set a reference to the entire
'range to be sorted
Set sortRange = ActiveSheet. _
Range(firstColToSort & "2:" & _
lastColToSort & lastRow)
'set a reference to the first sort key
Set sKey1 = _
ActiveSheet.Range(firstKeyCol & 2)
'set a reference to the second sort key
Set sKey2 = _
ActiveSheet.Range(secondKeyCol & 2)
'improve performance speed
Application.ScreenUpdating = False
'perform the sort
sortRange.Sort Key1:=sKey1, Order1:=xlAscending, Key2:=sKey2, _
Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
'housekeeping
Set sKey1 = Nothing
Set sKey2 = Nothing
Set sortRange = Nothing
End Sub
 
Back
Top