VBA Excel Range - Sort by few columns

Question

I am trying to sort an excel table that looks like this (this is a small snapshot. It has hundreds of rows and many more columns):

Here's my vba code

With inputSheet.Sort
    .SetRange Range("B3", inputSheet.Range("B3").End(xlToRight).End(xlDown))
    .SortFields.Add Key:=inputSheet.Range("C3"), Order:=xlAscending 'FieldSize
    .SortFields.Add Key:=inputSheet.Range("H3"), Order:=xlDescending 'Bracket complexity score
    .Header = xlYes
    .Apply
End With

This code leads to an error on .SortFields.Add "the sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank."

Any thoughts on how I could correct for this?

Additional comments
1) In one of my previous versions, I had it as .End(xlDown).End(xlToRight) (down first, then right). That one never worked. I thought I found a brilliant solution with .End(xlToRight).End(xlDown). It worked for a few days, but then stopped working.

See [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for how to find the last cell — BigBen, Dec 30 '19 at 19:36
It's best to use the max columns and/or rows and then go back. So `range("a" & rows.count).end(xlUp).row` for example — Nathan_Sav, Dec 30 '19 at 19:47
To sort specific range instead of the sheet, try the [`Range.Sort`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.sort) method. You can also try Record Macro to compare the generated code — Slai, Dec 30 '19 at 20:34

score 1 · Accepted Answer · answered Dec 30 '19 at 20:35

At first I thought the error was in Range selection. Turns out the problem is that excel saves sort criteria between sessions (it's saved within the file).
Every time I run the code, excel was throwing the error because the sort criteria was duplicate. So, the fix was to clear all criteria first.

With inputSheet.Sort
    .SortFields.Clear
End With

VBA Excel Range - Sort by few columns

1 Answers1