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

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

Answers


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:

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

Follow this tested code for 7 columns with dinam last cell. By 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

Need Your Help

Problem using custom HttpHandler to process requests for both .aspx and non-extension pages in IIS7

iis-7 httphandler integrated-pipeline-mode wildcard-mapping staticfilehandler

I am trying to process both ".aspx" and non-extension page requests (i.e. both contact.aspx and /contact/) using a custom HttpHandler in IIS7. My handler works just fine in either one case or the o...