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.

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
– Nathan_Sav, Commented Dec 30, 2019 at 19:47
To sort specific range instead of the sheet, try the Range.Sort method. You can also try Record Macro to compare the generated code — Slai
– Slai, Commented Dec 30, 2019 at 20:34

Dr Phil · Accepted Answer · 2019-12-30 20:35:04Z

1

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

answered Dec 30, 2019 at 20:35

Dr Phil

8991 gold badge11 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Excel Range - Sort by few columns

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related