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

ZoolPool · Apr 13th 2018

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:

Code

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!

Thanks

ZoolPool · Apr 18th 2018

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?

Thanks

gijsmo · Apr 25th 2018

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.

Code

Sub SortSelection()
  Dim rSort As Range
  
  'prompt for a range to be sorted
  Set rSort = Application.InputBox(Prompt:="Enter the range to sort (4 columns):", Title:="Specify Range", Type:=8)
  If rSort Is Nothing Then Exit Sub
  
  If rSort.Columns.Count <> 4 Then
    MsgBox "Must select 4 columns"
    Exit Sub
  End If
  
  With rSort.Parent.Sort
    With .SortFields
      .Clear
      'this assumes all 4 columns are to be sorted from leftmost to rightmost in ascending order
      .Add Key:=Range(rSort.Columns(1).Address), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .Add Key:=Range(rSort.Columns(2).Address), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .Add Key:=Range(rSort.Columns(3).Address), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .Add Key:=Range(rSort.Columns(4).Address), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    End With
    
    .SetRange Range(rSort.Address)
    .Header = xlYes                '<--- this assumes the top row is a header
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    
  End With
End Sub

Display More

ZoolPool · Apr 27th 2018

Thanks gijsmo, that works perfectly for what I needed.

I'll put more examples in my next question!

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

Participate now!

Share

Users Viewing This Thread