VBA Macro to sort a highlighted range, by multiple columns.

  • Hi All

    I want to create a macro to stop me having to Data>Sort and select four columns in order every time. So it allows me to sort a highlighted range, by multiple columns. The two issues I have are:

    I cant control and down to the last row, because there will be blanks in the data.

    The range will change size and locations a lot, hence why I'm highlighting the data.

    Part of my code for example:

    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("F14:F21") _, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    May include Row 17 as a blank so I cant control down, and the next set of data to sort could be B3:B10. I can always highlight the range, but I cant get VBA to sort that highlighted range.

    All help welcomed!


  • Just for my reference, I didnt get a response so what did I do wrong with my post?
    Should I give the data as an example too? or the full code?


  • Without more specifics, it's hard to determine what you are trying to do. A sample workbook with any sensitive information removed or obscured is often helpful.

    A generic piece of code that can sort 4 columns that you select is below, it uses a range prompt so you can select the area to be sorted (you can use the mouse to select the range once prompted).
    Again, without further information, the code assumes you want to sort all 4 columns from left to right in ascending order as opposed to eg, sorting all 4 columns based on just the leftmost column values.
    This also assumes a header is included in the selection so the top row is excluded from the sort.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!