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
– Dmitry Pavliv, Commented Feb 1, 2014 at 19:04
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
– newtoVBA, Commented Feb 1, 2014 at 19:11
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. :) — WGS
– WGS, Commented Feb 1, 2014 at 19:21
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
– newtoVBA, Commented Feb 1, 2014 at 20:01

Dmitry Pavliv · Accepted Answer · 2014-02-10 14:32:01Z

4

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

edited Feb 10, 2014 at 14:32

answered Feb 1, 2014 at 19:33

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

newtoVBA Over a year ago

that only seemed to sort the Name column, but the Date column was not sorted..? Thank you though, i will try tweaking this

Dmitry Pavliv Over a year ago

Code sorts on both columns, at first it sorts worksheet on names, and then sorts each group of identical names (if they exist) on date.

newtoVBA Over a year ago

ok, i had some blank values in the "Date" column, but once i deleted those rows out first, the macro worked. i built a separate macro that deletes out those rows, but when i add it to the beginning of your macro, it does not work correctly. any thoughts? Rows("1:1").Select Selection.Find(What:="Date", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.EntireColumn.Select Selection.SpecialCells(xlCellTypeBlanks).Select Selection.EntireRow.Delete

Dmitry Pavliv Over a year ago

can you provide some test workbook (i.e. using dropbox.com)? because for me code works perfectly

Dmitry Pavliv Over a year ago

btw, have you changes sheet name in line ThisWorkbook.Worksheets("Sheet1") to the name that is correct for you?

|

user3151675 · Accepted Answer · 2017-07-19 15:01:19Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related