|19 Jul 2011||#1|
| || |
EXCEL VBA Macro Sort Groups of Items
I am working on a code that has a series of metrics listed in rows. Each metric is uniquely specified by several headings listed in the header row. The (relevant) categories in the header are as follows:
The entire worksheet needs to be sorted.
Because the heading labeled "Metric" can have multiple row entries of the same value (string), I want to write a macro in which the rows with all the same entries under the metric header are grouped together, and sorted until each of the categories individually are sorted. Then, I want the entire sheet to be sorted. However, by doing this, I do not want the groups with the same "Metric" category labels to become unsorted. I want them to be "glued" together, and sorted as if the group were one row. The highest Priority of the group will determine where it is placed on the worksheet (Priority is determined by the labels 1,2,3,and ?. It is sorted in descending order - question mark last)
Here is some code I have been working on:
Dim ws As Worksheet Dim rowCount, subRowCount, idCount, letterCount, totCount, marker As Integer Dim letString, idLabel, curCell As String Dim rowSelect As Long 'Predefine necessary variables rowCount = 2 'Start at first row, excluding header idCount = 1 letterCount = 0 'Find the last row with data in the sheet. Number of last row = totCount totCount = ActiveSheet.Range("A9999").End(xlUp).Row 'Loop through each cell in column D to determine if consecutive metrics have the same value 'If true, while loop sorts each series of consecutive metrics individually 'LATER, CONVER THIS INTO FIND ALL!!!! Do curCell = ActiveSheet.Cells(rowCount, 4) 'Selects cell in 'metric' column at row rowCount subRowCount = rowCount + 1 'Makes a cell selection while cell values in column 4 are equal If curCell = ActiveSheet.Cells(subRowCount, 4) Then '<<<NECESSARY? ActiveSheet.Cells(rowCount, "L") = marker Do 'Resize selection to include cell with same metric name Selction.Resize(1).Select 'increment compare next two subesquent cells in sequence rowCount = rowCount + 1 subRowCount = rowCount + 1 curCell = ActiveSheet.Cells(rowCount, 4) 'Establish place holder in col "L" (empty) to track groups ActiveSheet.Cells(rowCount, "L") = marker Loop While curCell = ActiveSheet.Cells(subRowCount, 4) 'to prevent subsequent groups of metrics from being sorted together marker = marker + 1 'Sort the existing selection. Should this be selection.col("")? Selection.EntireRow.Sort Key1:=col("G"), Order1:=xlDescending, Key2:=col("B"), _ Order2:=xlDescending, key3:=col("C"), Order3:=xlDescending, key4:=col("D"), _ order4:=xlDescending, key5:=col("E"), Order5:=xlDescending '^^^In the future we will want the preceeding code to find all similar cells Else rowCount = rowCount + 1 End If Loop Until rowCount = totCount 'Sort through remaining worksheet while keep cell groups intact 'Should treat cells a ActiveSheet.EntireRow.Sort Key1:=col("G"), Order1:=xlDescending, Key2:=col("B"), _ Order2:=xlDescending, key3:=col("C"), Order3:=xlDescending, key4:=col("D"), _ order4:=xlDescending, key5:=col("E"), Order5:=xlDescending 'resets rowCount to start at the first row (excluding header) rowCount = 2 'Loops through each cell, looks at column L, if marker is present, 'id labels loop through letterCount to find appropriate ID label Do If ActiveSheet.Cells(rowCount, "L") = ActiveSheet.Cells(rowCount + 1, "L") Then 'Labels sub-metirc "idLabel-rowCount"A,B,C,etc."" for as long as marker is present in column L Do ActiveSheet.Cells(rowCount, "A") = idLabel & rowCount.String & letString(letterCount) 'trying to index string letterCount = letterCount + 1 rowCount = rowCount + 1 Loop While ActiveSheet.Cells(rowCount, "L") <> Empty _ And ActiveSheet.Cells(rowCount, "L") = ActiveSheet.Cells(rowCount + 1, "L") Else: ActiveSheet.Cells(rowCount, "A") = idLabel & rowCount.String End If letterCount = 1 rowCount = rowCount + 1 Loop While rowCount <> totCount 'EVENTUALLY WE WILL WANT TO COMBINE TWO LOOPS SO PROGRAM RUNS MORE EFFICIENTLY
There exists another caveat. Some of these groups will have hidden cells, which are referenced by a list validation adjacent to the hidden cells. If possible, I want to be able to sort the list, while keeping the hidden cells intact. As in, the clusters of hidden cells will be grouped together with row where the list is located and sorted within the whole list but not within the cluster itself (Just like the groups previously mentioned).
I also want to maintain the presence of the list, as I have noticed the lists completely disappear when I use the sort command in excel.
Can anyone help me with the first and/or second part of the questions? This seems rather tough so I appreciate your help greatly.
P.S. I am new to macros so please correct me if I am not using code correctly
|My System Specs|
|Similar help and support threads for2: EXCEL VBA Macro Sort Groups of Items|
|Excel Macro||Microsoft Office|
|sort sent items||Browsers & Mail|
|EXCEL VBA 2007 Macro: Offsetting cell form a user defined selection||Microsoft Office|
|EXCEL Sort sequence in Foreign Lamguage||Microsoft Office|
|Excel 2003 Macro Help?||Microsoft Office|
|How do I sort Start Menu items?||Customization|
|uac crashes excel macro||Microsoft Office|