VBA code to sort multiple columns by column name, differing locations

Question

I am new to VBA coding and would like a VBA script that sorts multiple columns. I first sort column F from smallest to largest, and then sort column K. However, I would like the Range value to be dynamic based on the column name rather than location (i.e. the value in column F is called "Name", but "Name" won't always be in column F)

I'm looking to change all of the Range values in the macro, and am thinking of replacing it with a FIND function, am I on the right track?

I.e. Change Range _ ("F1:F10695")

to something like Range (Find(What:="Name", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Range(Selection, Selection.End(xlDown)).Select

I have also seen some VBA script templates that use the Dim and Set functions to create lists, i.e. set x="Name", then sort for X in the matrix. Is that a better approach? Thank you for your help, I've attached the basic VBA script template below

Sub Macro2()
'
' Macro2 Macro
'

'
    Selection.AutoFilter
    Range("F1").Select
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("F1:F10695"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("K1").Select
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("K1:K10695"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

so, you need to sort always by column "Name" and then by column "K"? what would be if "Name" is in "K"? and what columns (I mean whole range) do you need to sort? — Dmitry Pavliv
in the code above, column F is titled "Name", and column K is titled "Date". So I would always like to sort by "Name" first, and then "Date", but they won't always be in column F and column K. I would like the code to encompass the entire worksheet. thank you — newtoVBA
Sounds like a simple issue of using Application.Match. I think simoco has an idea for this already, but it would help if you provide to us the entire range coverage, even if the header positions change. Where's your data, ie. A:AX, C:Z, etc.? Also, the sort order for Date, is it increasing as well? You mentioned ` i first sort column F from smallest to largest, and then sort column K` which is still kind of vague. :) — NullDev
could you explain how the application.match would work? For clarification, the data range is A1:V1000, but this is always changing. the reports I pull will have different column and row amounts/orders, but the column header of "Name" and "Date" will always be the same. It must sort Name increasing first, and then sort Date increasing. Thank you! — newtoVBA

Dmitry Pavliv Dmitry Pavliv · Accepted Answer · 2014-02-01T19:33:55

UPD:

Try this one:

Sub test()

    Dim rngName As Range
    Dim rngDate As Range
    Dim emptyDates As Range

    Dim ws As Worksheet
    Dim lastrow As Long


    Set ws = ThisWorkbook.Worksheets("Sheet1")

    With ws
        Set rngName = .Range("1:1").Find(What:="Name", MatchCase:=False)
        Set rngDate = .Range("1:1").Find(What:="Date", MatchCase:=False)

        If Not rngName Is Nothing Then
            lastrow = .Cells(.Rows.Count, rngName.Column).End(xlUp).Row
            On Error Resume Next
            Set emptyDates = .Range(rngDate, .Cells(lastrow, rngDate.Column)).SpecialCells(xlCellTypeBlanks)
            On Error GoTo 0
            If Not emptyDates Is Nothing Then
                emptyDates.EntireRow.Delete
            End If
        End If

        With .Sort
            .SortFields.Clear
            If Not rngName Is Nothing Then
                .SortFields.Add Key:=rngName, _
                    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            End If
            If Not rngDate Is Nothing Then
                .SortFields.Add Key:=rngDate, _
                    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            End If
            .SetRange ws.Cells
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With

End Sub

Notes:

change Sheet1 in line ThisWorkbook.Worksheets("Sheet1") to the sheet name that is true for you
code tries to find "Name" and "Date" in first row, and then, if this items found, adds SortFields, corresponding to that columns
as follow up from comments, OP wants also to delete rows with empty dates

VBA code to sort multiple columns by column name, differing locations

2 Answers