Dynamic Sorting

**pauldaddyadams** · 06-04-2013, 05:24 AM

Hi,

On the attached file I have a list of projects which are sorted by region, the regions are:
TOTAL - EMEA – USD
TOTAL - Americas – USD
TOTAL - EMPG – USD

Within each region I would like to sort the data by column BX to show the most profitable projects at the top of the list. I can do this manually but ideally I would like an automated version if possible?

Is there any way (macro or formula) in which I can achieve this? E.g. a macro or formula which first defines it’s own index (additional rows will be added), can it be that it looks for each instance of “TOTAL” to define the index last row? Then it sorts, then it moves onto the next region and so on.

Any help is greatly appreciated.

Paul

**OllieB** · 06-04-2013, 09:15 AM

This should work

Public Sub SortRegions()
   
   '#
   '# declare private variables
   '#
      Dim pvt_lng_RegioStartRow As Long
      Dim pvt_lng_LineNumber As Long
      
   '#
   '# initialise
   '#
      pvt_lng_RegioStartRow = 8
      
   '#
   '# loop for all rows on the current worksheet and identify the start and end rows of
   '# each region - once identified the rows within the region will be sorted on column BX
   '#
      With ActiveSheet
         For pvt_lng_LineNumber = 8 To (.Cells(.Rows.Count, "A").End(xlUp).Row + 1)
         
         '#
         '# if the contents of the cell in column A starts with the literal text "TOTAL" the
         '# last line of a region has been encountered
         '#
            If Left$(.Cells(pvt_lng_LineNumber, "A").Value, 5) = "TOTAL" Then
            
            '#
            '# sort the region data
            '#
               With .Sort
                  .SortFields.Clear
                  .SortFields.Add _
                     Key:=Range(Cells(pvt_lng_RegioStartRow, "BX"), Cells(pvt_lng_LineNumber - 1, "BX")), _
                        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
                  .SetRange Range(Cells(pvt_lng_RegioStartRow, "A"), Cells(pvt_lng_LineNumber - 1, "BX"))
                  .Header = xlNo
                  .MatchCase = False
                  .Orientation = xlTopToBottom
                  .SortMethod = xlPinYin
                  .Apply
               End With
                        
            '#
            '# the starting row for the next region is the current line number + 1
            '#
               pvt_lng_RegioStartRow = pvt_lng_LineNumber + 1
            End If
      
         Next pvt_lng_LineNumber
      End With
   
   End Sub

**pauldaddyadams** · 06-04-2013, 10:23 AM

Hi,

Thanks for this code - it didnt work, I am just trying to find out why now (if I can). I know its moving the total row which I need to avoid (these lines must remain fixed) e.g. the rows above and below the total lines are the only data that needs to be sorted.

I notice also that when the data is sorted it becomes corrupt because of the formulas used. Is there any way it can copy and past the data to a new sheet first and then perform the sort?

**OllieB** · 06-04-2013, 10:47 AM

I have tested my solution against the sample data provided by you and have no problems whatsoever.

**Marcol** · 06-04-2013, 12:03 PM

See if this formula approach helps, fror the Dynamic Named Ranges used see the Names Manager

On Sheet "Sorted", column A is an index/ranking for each region, and is hidden with the +/- grouping button.

Drag A8:B8 down
Drag L8 across to Column BY, then down

The TOTALS rows are highlighted with C/F