VBA code to sort multiple columns by column name, in different places

Question

I am new to VBA coding and would like a VBA script to sort multiple columns. First, I sort column F from smallest to largest, and then sort column K. However, I need the Range value to be dynamic based on the name of the column, not the location (that is, the value in column F is called "Name", the Name is not always will be in column F)

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

those. Change Range _ ("F1: F10695")

for 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 also saw some VBA script templates that use the Dim and Set functions to create lists, i.e. set x = "Name" and then sort by X in the matrix. Is this a better approach? Thank you for your help. I attached a 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

Answer 1

UPD:

Try the following:

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 Sheet1the line ThisWorkbook.Worksheets("Sheet1")to the sheet name that is true for you.
• the code tries to find "Name" and "Date" in the first line, and then, if these elements are found, adds the SortFieldscorresponding to these columns
• according to comments, OP also wants to delete rows with empty dates

Answer 2

7 dinam. RCC66

Sub Auto_Open()

' Order by
    Dim LastRow As Integer

    With ActiveSheet
        intLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    End With

    ActiveWorkbook.Worksheets("Invoices").Sort.SortFields.Clear

    ActiveWorkbook.Worksheets("Invoices").Sort.SortFields.Add Key:=Range( _
        "Q3:Q" & intLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

    ActiveWorkbook.Worksheets("Invoices").Sort.SortFields.Add Key:=Range( _
        "L3:L" & intLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

    ActiveWorkbook.Worksheets("Invoices").Sort.SortFields.Add Key:=Range( _
        "O3:O" & intLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

    ActiveWorkbook.Worksheets("Invoices").Sort.SortFields.Add Key:=Range( _
        "J3:J" & intLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

    ActiveWorkbook.Worksheets("Invoices").Sort.SortFields.Add Key:=Range( _
        "B3:B" & intLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

    ActiveWorkbook.Worksheets("Invoices").Sort.SortFields.Add Key:=Range( _
        "H3:H" & intLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

    ActiveWorkbook.Worksheets("Invoices").Sort.SortFields.Add Key:=Range( _
        "E3:e" & intLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

    With ActiveWorkbook.Worksheets("Invoices").Sort
        .SetRange Range("A1:R" & intLastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub