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 b…

Accepted Answer

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

Solution

Problem