Sort multiple columns by one column

Question

I'm making an email list in Excel with three columns. On the worksheet, I have two buttons, "sort by Name" and "sort by Date added". I would like to sort all three columns by the button chosen so I can find entries faster (I am also entering a separate lookup function later).

Basically, I want the sort function that's already on the toolbar in the worksheet where you can just press it and it knows which column to sort by already. I've seen things for macros and for VBA but all of them are sorting columns by separate parameters, whereas I need these columns linked.

Have you tried any code? If not, try recording a macro and see how it does it. That should give you an indication of what to do in your code. Then if you get stuck come back with the code you made and we'll help you fix it (because SO is not a code request site). — Tim
– Tim, Commented Oct 6, 2015 at 20:42
I'd like to clarify that I'm in no means looking for someone to write this for me- rather, I'd like direction in methods you've used before, commands that have worked well. For example, I didn't even know you could record a macro- the avenues I've taken before never made me realize I could. I'm looking in to that now — Miksoko
– Miksoko, Commented Oct 6, 2015 at 20:46

score 1 · Accepted Answer · 2015-10-06 21:08:40Z

The code produce by the recorder on a Range.Sort method is very verbose and can be chopped down quite a bit to what is essential.

If columns A:C were Name, Email, Date Added then this will sort by Name first, then Date Added.

with worksheets("sheet1")    '<~~ set this properly!
    with .cells(1, 1).currentregion    '<~~ assumes data starts in A2 with a header row in A1:C1
        .Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
                    Key2:=.Columns(3), Order2:=xlAscending, _
                    Orientation:=xlTopToBottom, Header:=xlYes
    end with
end with

This will sort by Date Added first, then Name.

with worksheets("sheet1")    '<~~ set this properly!
    with .cells(1, 1).currentregion    '<~~ assumes data starts in A2 with a header row in A1:C1
        .Cells.Sort Key1:=.Columns(3), Order1:=xlAscending, _
                    Key2:=.Columns(1), Order2:=xlAscending, _
                    Orientation:=xlTopToBottom, Header:=xlYes
    end with
end with

You can have up to 3 keys. Any more than that and you have to run the routine twice. The opposite of xlAscending is of course xlDescending.

siegeMind · Accepted Answer · 2015-10-06 21:00:23Z

The best way I've found to find something that's already on the toolbar is to use the "Macro Recorder" in a blank/new workbook, and then look at the code.

Are the columns adjacent to each other? Because if so you can use something like this;

//Alright, so this is if you wanted each of the columns to have their
//own values that you are sorting by, if you just want one criteria, 
//just use one of the lines
Dim varName as String
Dim varDate as String
Dim varExtra as String

ActiveSheet.Range("A:C").AutoFilter Field:=1, Criteria1:=varName
ActiveSheet.Range("A:C").AutoFilter Field:=2, Criteria1:=varDate
ActiveSheet.Range("A:C").AutoFilter Field:=3, Criteria1:=varExtra

Basically, it's saying for the three columns given, go find the field (which will correspond to a column) indicated and filter by the criteria. You can also use a string value in the Criteria spot instead of a variable.

Collectives™ on Stack Overflow

Sort multiple columns by one column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related