Sort reference not valid

Question

I'm trying to learn how to make a sort work from VBA and have created a very simple range of cells, a 2x10 grid with numbers 1-10 in the A column and various names of people in the B column (cannot upload a screenshot). The following VBA procedure produces the following error:

Private Sub Worksheet_Change(ByVal Target As Range)

[A1:B10].Sort A1, xlAscending

End Sub

"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."

I don't understand the error message. How can I make this work please?

Thanks,

Rip

Answer 1

All you really need to change (assuming the default sort options are ok) is A1 to [A1]:

Private Sub Worksheet_Change(ByVal Target As Range)

[A1:B10].Sort [A1], xlAscending

End Sub

Answer 2

Hello Ripper,

I'm Ibhadighi and I'd happily help you with your question. In this forum, we are Microsoft consumers just like yourself.

The error you're encountering typically happens when the range or criteria specified for the sort operation in VBA are not correctly defined. In your case, the sort code needs a bit of adjustment to specify the key by which you want to sort your range. Here's a corrected version of your code:

Private Sub Worksheet_Change(ByVal Target As Range) With Me.Range("A1:B10") . Sort Key1:=. Cells(1, 1), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With End Sub

This script sorts the range A1:B10 based on the values in the first column (A1:A10) in ascending order. Here’s the breakdown:

• With Me.Range("A1:B10"): Specifies the range to sort.
• Key1:=. Cells(1, 1): Defines the first cell in the range as the key to sort by.
• Order1:=xlAscending: Sets the sort order to ascending.
• Header:=xlGuess: Excel guesses if there's a header based on the data. Use xlNo if there's definitely no header or xlYes if there is.
• . Sort: Calls the sort function on the specified range.

Make sure this VBA script is placed within the sheet module in the Visual Basic for Applications (VBA) editor where you want the sort to happen. Also, be cautious with the Worksheet_Change event, as it will trigger this sort action every time a change is made to the worksheet, which might not always be desirable.

I hope this helps.

Best Regards, Ibhadighi