If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Utilizing a macro for a datasets that don't have a fixed number of
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. |
#2
|
|||
|
|||
Utilizing a macro for a datasets that don't have a fixed number of
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 "DyingIsis" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|