Skip to content
The Chat Archive
gpt-3.5-turbo-0613· OpenAI WildChat-4.8M (AI2)

I have a table with Headers. Can you please write me a VBA code that will sort the table…

  1. User

    I have a table with Headers. Can you please write me a VBA code that will sort the table column A to H, sorted by H Smallest to Largest

  2. Assistant

    Sure! Here's a VBA code that will sort the table column A to H, sorted by H (smallest to largest):

    Sub SortTableColumnAH()
        Dim ws As Worksheet
        Dim tbl As ListObject
        Dim rng As Range
        
        ' Set the worksheet and table variables
        Set ws = ThisWorkbook.Worksheets("Sheet1") ' Change "Sheet1" to your actual sheet name
        Set tbl = ws.ListObjects("Table1") ' Change "Table1" to your actual table name
        
        ' Set the range to sort (column A to H)
        Set rng = tbl.Range.Resize(tbl.Range.Rows.Count, 8) ' assuming your table has 8 columns (A to H)
        
        ' Turn off screen updating to speed up the macro execution
        Application.ScreenUpdating = False
        
        ' Sort the range by column H (index: 8), smallest to largest
        With ws.Sort
            .SortFields.Clear
            .SortFields.Add Key:=rng.Columns(8), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange rng
            .Header = xlYes
            .Apply
        End With
        
        ' Turn on screen updating and display a message
        Application.ScreenUpdating = True
        MsgBox "Table column A to H sorted by column H (smallest to largest)."
    End Sub
    

    Note:

    1. Replace "Sheet1" in Set ws = ThisWorkbook.Worksheets("Sheet1") with your actual worksheet name.
    2. Replace "Table1" in Set tbl = ws.ListObjects("Table1") with your actual table name.
    3. The code assumes your table has 8 columns (A to H). If your table has more or fewer columns, adjust the Resize function accordingly.

    To run this code, press ALT + F11 to open the VBA editor, insert a new module, and paste the code into the module. Then, you can run the macro by pressing ALT + F8, selecting the macro "SortTableColumnAH", and clicking "Run".

  3. User

    my table does not have a name

  4. Assistant

    If your table doesn't have a name, you can refer to it using the worksheet and range instead. Here's the modified code that sorts column A to H by column H (smallest to largest) for a table without a name:

    Sub SortTableColumnAH()
        Dim ws As Worksheet
        Dim tableRange As Range
        Dim rng As Range
        
        ' Set the worksheet variable
        Set ws = ThisWorkbook.Worksheets("Sheet1") ' Change "Sheet1" to your actual sheet name
        
        ' Set the range of the table (adjust the range as per the size of your table)
        Set tableRange = ws.Range("A1:H10") ' Change "A1:H10" to the range of your table
        
        ' Set the range to sort (column A to H)
        Set rng = tableRange.Resize(tableRange.Rows.Count, 8) ' assuming your table has 8 columns (A to H)
        
        ' Turn off screen updating to speed up the macro execution
        Application.ScreenUpdating = False
        
        ' Sort the range by column H (index: 8), smallest to largest
        With ws.Sort
            .SortFields.Clear
            .SortFields.Add Key:=rng.Columns(8), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange rng
            .Header = xlYes
            .Apply
        End With
        
        ' Turn on screen updating and display a message
        Application.ScreenUpdating = True
        MsgBox "Table column A to H sorted by column H (smallest to largest)."
    End Sub
    

    Make sure to adjust the range tableRange in the code to match the actual range of your table. Then you can follow the same instructions mentioned earlier to run the code.