How to sort columns with headers alphabetically using VBA-CodePudding

Sorry in advance as this question has most likely been asked, however I can't seem to find a solution that works - I have a macro that takes in a user's input, searches an SQL Database for the information and then takes that information and pastes it into a table and will sort alphabetically. The only issue I have is the sorting, 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 as well as an image of the original test dataset, what happens after my macro, and what it actually should be:

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

CodePudding user response：

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

CodePudding user response：

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