VBA - Sort sheet but use a Range that is a variable

[JeffHaas]

I've searched on this problem for quite awhile and can't find a relevant answer.

I'm trying to select a column on a worksheet, sort by the values in the column (high to low), copy the values in A2:A25 to another worksheet...then move over a few columns, sort by the values there, and copy the values in column A to another worksheet again...etc.

Probably easier to understand with a fictionalized example:

Food     England     France     Germany
Apples    10,000     15,000      20,000
Pears     5,000      11,000       8,000
Grapes   11,000       7,000        6,000

There is text in column A and different values for each country in columns to the right. The idea is to sort the spreadsheet by a country, then copy the text in column A to another place. Repeat for each country.

The issue I'm running into is setting the Range in the Sort.Sortfields.Add.Key command by something other than a direct reference.

I would like to update this range via a variable in a For loop. This is the code at this point:

Sub GeoCount_copy_paste()' Test for copying multiple sorted interests into one column


Dim GeoCount As Integer
Dim Index As Integer
Dim i As Integer

' Count the number of countries on another worksheet
GeoCount = Application.CountA(Worksheets(2).Columns(1)) 


Index = 1


For i = 1 To (GeoCount - 1) ' Accounts for header row


' ** Sort country by interest **
    Sheets(1).Select
    Range("A2").Activate
    ActiveCell.Offset(0, 5 * Index).Select
 
'    Range("H2").Select
    
    ActiveWorkbook.Worksheets(1).Sort.SortFields.Clear

' This is the command I can't get to work with a variable instead of "F2"
    ActiveWorkbook.Worksheets(1).Sort.SortFields.Add Key:=Range( _
        "F2"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal  


    With ActiveWorkbook.Worksheets(1).Sort
        .SetRange Range("A2:DR101")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


    ' Copy the top 25 interests
    Range("A2:A26").Select
    Selection.Copy


    ' Go to second sheet
    Sheets(2).Select
    Range("C2").Select
    ActiveSheet.Paste


    Index = Index + 1 ' Increment the index for the next loop


Next i


End Sub

I see how in other situations you can change a Range reference via a variable, but I haven't gotten any of them to work in this case.

 

[TheSpiderman]

Hello,
Not sure if it is what you need, because you don't tell us where the variable come from

Sub GeoCount_copy_paste() ' Test for copying multiple sorted interests into one column

Dim GeoCount As Integer
Dim Index As Integer
Dim i As Integer
Dim lColNumber As Long

' Count the number of countries on another worksheet
GeoCount = Application.CountA(Worksheets(2).Columns(1))

Index = 1
lColNumber = 2 ' Start at column B

For i = 1 To (GeoCount - 1) ' Accounts for header row

' ** Sort country by interest **
    Sheets(1).Select
    Range("A2").Activate
    ActiveCell.Offset(0, 5 * Index).Select
 
'    Range("H2").Select
    
    ActiveWorkbook.Worksheets(1).Sort.SortFields.Clear

' This is the command I can't get to work with a variable instead of "F2"
' The column is now variable
    ActiveWorkbook.Worksheets(1).Sort.SortFields.Add Key:=Cells( _
        2, lColNumber), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
'Increment the column number
    lColNumber = lColNumber + 1

    With ActiveWorkbook.Worksheets(1).Sort
        .SetRange Range("A2:DR101")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    ' Copy the top 25 interests
    Range("A2:A26").Select
    Selection.Copy

    ' Go to second sheet
    Sheets(2).Select
    Range("C2").Select
    ActiveSheet.Paste

    Index = Index + 1 ' Increment the index for the next loop

Next i

Have a nice day !
Den

 

[rlv01]

Not sure I understand what you are trying to do w/resp to copying column A to other sheets. Here's a simple example of how to use a variable range to do a sort

Sub SortExample()


    Dim WS1 As Worksheet
    Dim DataRange As Range, HeaderRange, R As Range


    'Assumes your England / France data table is on "sheet1"
    Set WS1 = ActiveWorkbook.Worksheets("Sheet1")


    'Assumes the first row contains column headers
    Set HeaderRange = Application.Intersect(WS1.UsedRange, WS1.Range("A1").EntireRow)
    Debug.Print HeaderRange.Address


    'Set the data block to be sorted
    Set DataRange = Application.Intersect(WS1.UsedRange, HeaderRange.EntireColumn)
    Debug.Print DataRange.Address


    'Sort each column in turn
    For Each R In HeaderRange
        If R.Value <> "Food" Then    '(no need to bother sorting the food column)
            'This is one way to sort using a variable range. Note I'm sorting a range, not the whole worksheet
            DataRange.Sort Key1:=R, Order1:=xlDescending, Header:=xlYes
            MsgBox "Sorted by " & R.Value
        End If
    Next R
End Sub

 

[Takae]

Please make a new file and try following code with the sample data you showed.

Sub test()
Dim c, ws
Dim WS1 As Worksheet
Dim LR As Long, flg As Long
Set WS1 = Sheets("Sheet1")
Application.ScreenUpdating = False
With WS1
    LR = .Cells(Rows.Count, 1).End(xlUp).Row
    For Each c In .Range(.Range("B1"), .Cells(1, Columns.Count).End(xlToLeft))
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Cells(2, c.Column), SortOn:=xlSortOnValues, Order:=xlDescending
        With .Sort
            .SetRange WS1.Range(WS1.Range("A2"), WS1.Cells(LR, Columns.Count))
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        For Each ws In Worksheets
            If ws.Name = c Then
                flg = 1
                Exit For
            End If
        Next
        
        If flg = 0 Then
            Worksheets.Add after:=Worksheets(Worksheets.Count)
            Range("C2:C25").Value = .Range("A2:A25").Value
             ActiveSheet.Name = c
        Else
            Sheets(c.Value).Range("C2:C25").Value = .Range("A2:A25").Value
            flg = 0
        End If
    Next
End With
Application.ScreenUpdating = True
End Sub

 

[JeffHaas]

First, thank you all for the help! Some comments:


- I may not have made things clear in my description. The intent of this is to do the equivalent of clicking on a column, then selecting:
Home tab > Sort & Filter > Sort Largest to Smallest > Expand the selection


This sorts the entire sheet by the values in the column, and keeps the data in the rows together. The result is you can find out what the biggest to smallest values in column A are for each country. After the sort, the loop takes the values and copies them to another sheet for making a report. There is still some work to be done in that part.


I also didn't mention that the countries in the other spreadsheet are not right next to each other, but each one is five columns over (which is why I move the selection over a few columns each time through the loop).


Spiderman:
Your friendly, neighborhood code works with one small tweak...I needed to increment the column number at the end of the loop, not the start. Your code also let me drop this line, which moves the cursor to another part of the sheet: ActiveCell.Offset(0, 5 * Index).Select
I was assuming that I could select the ActiveCell and use it to set the key in Sort.Sortfields, but that doesn't work.


rlv01:
This works too, but I can't see how to increment R so that it can move over more than one column. I tried adding R = R + 6 to the loop but then realized that R is defined as a Range, not an Integer or a Long.


Takae:
Thank you, your refactoring of the code is very interesting and I will study it to understand the new features you added.

 

[rlv01]

but I can't see how to increment R so that it can move over more than one column. I tried adding R = R + 6 to the loop but then realized that R is defined as a Range, not an Integer or a Long.

With a simple mod you can make it sort only on the columns you want, even if they are separated by other columns.

(not tested)

Sub SortExample()
    Dim DoSort As Boolean
    Dim WS1 As Worksheet
    Dim DataRange As Range, HeaderRange, R As Range

    'Assumes your England / France data table is on "sheet1"
    Set WS1 = ActiveWorkbook.Worksheets("Sheet1")

    'Assumes the first row contains column headers
    Set HeaderRange = Application.Intersect(WS1.UsedRange, WS1.Range("A1").EntireRow)
    Debug.Print HeaderRange.Address

    'Set the data block to be sorted
    Set DataRange = Application.Intersect(WS1.UsedRange, HeaderRange.EntireColumn)
    Debug.Print DataRange.Address

    'Sort each column in turn
    For Each R In HeaderRange
        DoSort = True
        Select Case R.Value
        Case "England"
        Case "France"
        Case "Germany"
        Case Else
            DoSort = False    'Skip all columns except the ones you want to sort.
        End Select

        If DoSort Then
            DataRange.Sort Key1:=R, Order1:=xlDescending, Header:=xlYes
            MsgBox "Sorted by " & R.Value
        End If
    Next R
End Sub

 

[TheSpiderman]

...
I was assuming that I could select the ActiveCell and use it to set the key in Sort.Sortfields, but that doesn't work.
...

Hi JeffHaas,
You can use the active cell if you want, just replace lColNumber by Selection.Column like this

[COLOR=#333333]    ActiveWorkbook.Worksheets(1).Sort.SortFields.Add Key:=Cells( _
[/COLOR]        2, Selection.Column), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal

Have a nice day!
Den

 

[JeffHaas]

Hi Den,

Thanks for the tip!

Jeff