How to sort columns with headers alphabetically using VBA

Question

I have a macro that takes a user's input, searches an SQL Database for the information and then pastes that information into a table.

I am able to sort one column alphabetically, however the adjacent columns do not get sorted as well.

Below is a snippet of the code for sorting:

Sub testSort()
'Sheets(1).Range("A2", Sheets(1).Range("A2").End(xlDown)).Select
'Sheets(1).Range("A2:" & Sheets(1).Range("A2").End(xlDown).Address).Select
Dim testrange As Range
Set testrange = Sheets(1).Range("A2", Sheets(1).Range("A2").End(xlDown))
testrange.Sort key1:=Range("A2"), _
  order1:=xlAscending, _
  Orientation:=xlSortColumns
End Sub

Image of the original test dataset, what happens after my macro, and what it should be:

The range you are using for sorting, testrange, is just a single column. this means it sorts the names in column A but does not move the Numbers in column B to the name's new position. To fix this, include column B in testrange like Sheets(1).Range("A2", Sheets(1).Range("B2").End(xlDown)) — Toddleson
– Toddleson, Commented Oct 14, 2021 at 18:17

Toddleson · Accepted Answer · 2021-10-14 18:27:37Z

The range you are using for sorting, testrange, is just a single column. this means it sorts the names in column A but does not move the Numbers in column B to the name's new position. To fix this, include column B in testrange like

Set testrange = Sheets(1).Range("A2", Sheets(1).Range("B2").End(xlDown))

testrange now includes columns A and B.

To make this loop over multiple adjacent tables, we can use a variable for the starting position and then move the starting position for each loop.

Sub testSort()

Dim topCorner As Range
Set topCorner = Sheets(1).Range("A2")

For i = 1 To 3
    Dim testrange As Range
    Set testrange = Sheets(1).Range(topCorner, topCorner.End(xlDown).Offset(, 1))
    testrange.Sort Key1:=testrange.Columns(1), _
                   Order1:=xlAscending, _
                   Orientation:=xlSortColumns
    
    Set topCorner = topCorner.Offset(, 3)
Next
            
End Sub

bgado · Accepted Answer · 2021-10-14 18:27:19Z

0

Below is the updated code that was successful

Sub testSort()

Dim testrange As Range
Set testrange = Sheets(1).Range("A2", Sheets(1).Range("B2").End(xlDown))
testrange.Sort key1:=Range("A2:B2"), _
order1:=xlAscending

End Sub

answered Oct 14, 2021 at 18:27

bgado

411 silver badge9 bronze badges

Collectives™ on Stack Overflow

How to sort columns with headers alphabetically using VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related