Looping Through, and sorting the contents of Named Ranges

[TurningFinal]

Hi All,
I hate to be 'that guy', but I cant seem to work this one out on my own. Please note that I have zero training in Excel or VBA, I've simply taught myself to google quite well.

Problem is as follows.

I have a large scheduling spreadsheet, which is populated via a macro from a SAP dump.

It puts the data into areas based on shift and date of the work to be completed. The code I've written requires that each days and areas worth of work be selected, and then when i run it, it sorts and moves it all to the top of the selection (as part of sorting).

I have gone through the sheet and created a heap of named ranges where the data is initially dumped, and I'm trying to loop through each range and run the same code.

So as you can see the 1st set of commented out code and what is in blue is what im trying to do. The uncommented code works, but requires i make the selections manually.
I think that if i can get each Name looped through, the rest of the code will work. Happy to listen to any ideas you may have regarding my code in general.

Option Compare Text



'mapped to mouse 3 via Autohotkey on ctrl-shift-Q






Sub SortByFLOC()




Dim i As Integer


[COLOR=#0000ff]'Sort by functional location description.[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]' Dim N As Name[/COLOR]
[COLOR=#0000ff]'    For Each N In ActiveWorkbook.Names[/COLOR]
[COLOR=#0000ff]'[/COLOR]
[COLOR=#0000ff]'        N.SortFields.Add Key:=Selection.columns(6), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers[/COLOR]
[COLOR=#0000ff]'        N.SortFields.Add Key:=Selection.columns(8), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers[/COLOR]
[COLOR=#0000ff]'        N.SortFields.Add Key:=Selection.columns(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers[/COLOR]
[COLOR=#0000ff]'        N.SetRange N[/COLOR]
[COLOR=#0000ff]'        N.Apply[/COLOR]
[COLOR=#0000ff]' Next N[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]    [/COLOR]
[COLOR=#0000ff] With ActiveSheet.Sort[/COLOR]
[COLOR=#0000ff]        .SortFields.Clear[/COLOR]
[COLOR=#0000ff]        .SortFields.Add Key:=Selection.columns(6), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers[/COLOR]
[COLOR=#0000ff]        .SortFields.Add Key:=Selection.columns(8), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers[/COLOR]
[COLOR=#0000ff]        .SortFields.Add Key:=Selection.columns(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers[/COLOR]
[COLOR=#0000ff]        .SetRange Selection[/COLOR]
[COLOR=#0000ff]        .Apply[/COLOR]
[COLOR=#0000ff]    End With[/COLOR]
    


' Set font to arial, size to 12 in case of any manually entered lines


    With Selection.Font
    .Name = "Arial"
    .Size = 12
    End With
    
'If a cell is highlighted RGBorange AND bolded, it will retain its bolding, but if it is bold without color, it will be de-bolded
    
For Each c In Selection
            If c.Value Like "*switch*" Then GoTo BypassFormat
            If c.Value Like "*hr*" Then
                c.Font.bold = True
                c.Interior.Color = rgbOrange
BypassFormat:
            End If
            
            If c.Font.bold = True And c.Interior.Color = rgbOrange And c.Value = "" Then
                    c.Interior.Color = xlNone
                    c.Font.bold = False
            End If
             
                    If c.Font.bold = True And c.Interior.Color <> rgbOrange Then
                        c.Font.bold = False
                    End If
             
        Next
    
    
    


'horizontal align the hours and man allocations
'change for i = 6 to 9 digits to reflect columns to be centered


    For i = 6 To 9
        Selection.columns(i).HorizontalAlignment = xlCenter
    Next i
    
    For i = 1 To 1
        Selection.columns(i).HorizontalAlignment = xlCenter
    Next i




        


'set the 1st column to the grey already in the schedule


        For col = 1 To 1
            Selection.columns(col).Interior.Color = RGB(242, 242, 242)
        Next col
                
'set the right hand border of the 1st column to the same as the schedule already has
                
With Selection
    Set Rng = Selection.columns(1)
End With


With Rng.Borders(2)
    .LineStyle = 1
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = 2
End With






        
        
End Sub

 

[Colo]

Hi TurningFinal,
First, good job! I think that it's almost done. All you have to do is that making the uncommented code part as a sub routine then call it from the main routine. I have no data for testing and didn't test. Also I have no idea what you select on a worksheet. So need to amend this code to suit to your needs. (it's just an example how to call a sub routine.)

Sub SortByFLOC_Test()
    Dim sh As Worksheet, rng As Range
    For Each N In ActiveWorkbook.Names

        'Get a range from Named range
        With N.RefersToRange
            Set sh = Sheets(.Parent.Name)
            Set rng = sh.Range(.Address)
        End With

        N.SortFields.Add Key:=Selection.Columns(6), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
        N.SortFields.Add Key:=Selection.Columns(8), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
        N.SortFields.Add Key:=Selection.Columns(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
        N.SetRange N
        N.Apply

        Call SubRootine(rng)
    Next
End Sub


Sub SubRootine(Target As Range)
    Dim i As Integer

    ' Set font to arial, size to 12 in case of any manually entered lines

    With Target.Font
        .Name = "Arial"
        .Size = 12
    End With

    'If a cell is highlighted RGBorange AND bolded, it will retain its bolding, but if it is bold without color, it will be de-bolded

    For Each c In Target
        If c.Value Like "*switch*" Then GoTo BypassFormat
        If c.Value Like "*hr*" Then
            c.Font.Bold = True
            c.Interior.Color = rgbOrange
BypassFormat:
        End If

        If c.Font.Bold = True And c.Interior.Color = rgbOrange And c.Value = "" Then
            c.Interior.Color = xlNone
            c.Font.Bold = False
        End If

        If c.Font.Bold = True And c.Interior.Color <> rgbOrange Then
            c.Font.Bold = False
        End If

    Next

    'horizontal align the hours and man allocations
    'change for i = 6 to 9 digits to reflect columns to be centered


    For i = 6 To 9
        Target.Columns(i).HorizontalAlignment = xlCenter
    Next i

    For i = 1 To 1
        Target.Columns(i).HorizontalAlignment = xlCenter
    Next i


    'set the 1st column to the grey already in the schedule
    For col = 1 To 1
        Target.Columns(col).Interior.Color = RGB(242, 242, 242)
    Next col

    'set the right hand border of the 1st column to the same as the schedule already has

    With Target
        Set rng = Target.Columns(1)
    End With

    With rng.Borders(2)
        .LineStyle = 1
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = 2
    End With
End Sub

 

[TurningFinal]

Hi, and thanks for the reply Colo.

When I run your version of my code, i get a run time 438 code on the line.

any ideas what might be wrong that line?

[COLOR=#333333]N.SortFields.Add Key:=Selection.Columns(6), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers[/COLOR]

 

[TurningFinal]

If it will help, i can post a sample of the data that is to be sorted.