VBA Select all data and sort based on column header "Name"

Question

I'm struggling to figure out how I can select all data, and sort based off of a column header name of "Service Ticket". There are plenty of examples on how to sort off of a "hard-coded" column. My issue is the imported data sheet often might have a different number of columns, causing the column of interest to change.

BDra · Accepted Answer · 2020-07-10 07:46:06Z

1

Sounds like you have already found VBA to sort by a given column. Use Find to locate the header you are interested in, then assign the column number to a variable and use that variable in place of a static column reference.

Sub SortSomeColumn()

    Dim SvcTicketRange As Range
    Dim SortColumn As Integer

    With MyWorksheet
    
        Set SvcTicketRange = .Rows(1).Find(What:="Service Ticket", After:=.Cells(1, 1), LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        
        If Not SvcTicketRange Is Nothing Then
            SortColumn = SvcTicketRange.Column
            
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=Range(.Cells(1, SortColumn), .Cells(100, SortColumn)), _
                SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                
            With .Sort
                .SetRange Range("A1:E100")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
            
        Else
            'define what you want to do if the Service Ticket header is not found
            
        End If
        
    End With
    
End Sub

answered Jul 10, 2020 at 7:46

BDra

5361 gold badge4 silver badges14 bronze badges

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

2 Comments

BBridges Over a year ago

Awwh... Thank you! This makes sense. I'll give it a shot.

BDra Over a year ago

Good luck, and don't forget to accept the answer if it solves the issue.

Collectives™ on Stack Overflow

VBA Select all data and sort based on column header "Name"

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related