Sort rows by column value

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Sort rows by column value

Post by ABabeNChrist »

I need some assistance with code to sort from range A1:A1000. I want to sort rows by column value. I am using an X to identify employee position in either column C, D E or F

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sort rows by column value

Post by HansV »

So how exactly do you want to sort the data? Please explain in detail, with some examples.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Sort rows by column value

Post by ABabeNChrist »

I would like to be able to sort rows when needed using a command button. I am using an X in either column C, D, E or F to identify the employee’s title. I would like all rows that have an X in column C to be alphabetized first, the column D, E and then column F. Range of rows A1:A1000. Sample workbook attached
Sample.xlsm
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sort rows by column value

Post by HansV »

This was confusing - some of the empty-looking cells in columns C to F contain a space, others don't.
You can assign the following macro to a button:

Code: Select all

Sub SortData()
    Dim m As Long
    With Worksheets("Main Data Sheet")
        m = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("C3:F" & m).Replace What:=" ", Replacement:=""
        With .Sort
            .SortFields.Clear
            .SortFields.Add2 Key:=Range("C2")
            .SortFields.Add2 Key:=Range("D2")
            .SortFields.Add2 Key:=Range("E2")
            .SortFields.Add2 Key:=Range("F2")
            .SetRange Range("A2:J" & m)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .Apply
        End With
    End With
End Sub
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Sort rows by column value

Post by ABabeNChrist »

I was getting an error message. The reason for empty spaces is the employee position will change to one of the other possible selections from column C to F
error.jpg
Error line.jpg
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sort rows by column value

Post by HansV »

What happens if you replace all instances of Add2 with Add ?
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Sort rows by column value

Post by ABabeNChrist »

Yes that was it, thank you

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sort rows by column value

Post by HansV »

Add2 is only available in Excel 2016 and later, as far as I know.
Best wishes,
Hans