CODEWITHSUNDEEP
excelvba
Dr Phil
Dr Phil

Reputation: 817

VBA Excel Range - Sort by few columns

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):

enter image description here

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.

Upvotes: 1

Views: 497

Answers (1)

Dr Phil
Dr Phil

Reputation: 817

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

Upvotes: 1

Related Questions