VBA Macro code for automatic arrangement

Dear NMZ1133,

Thank you for posting in our Exceldemy forum. I have created a VBA macro that sorts cells in alphabetical order in Excel. After entering new data, you can run the macro by clicking a button on this worksheet. Please take a look at the image below for a better understanding:

1701321779952.gif
The VBA code that I have used is given below:
Code:
Sub SortAlphabeticallyDynamic()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim rng As Range
    Dim Starting_Cell As Range
    
    ' Set the worksheet
    Set ws = ActiveSheet
    Set Starting_Cell = Range("B3") 'Assuming that the data to be sorted excluding headings starts from cell B3
    
    ' Determine the last used row on the column of Starting_Cell
    lastRow = ws.Cells(ws.Rows.Count, Starting_Cell.Column).End(xlUp).Row
    
    ' Determine the last used column in row the row of Starting_Cell
    lastCol = ws.Cells(Starting_Cell.Row, ws.Columns.Count).End(xlToLeft).Column
    
    ' Set the dynamic range
    Set rng = ws.Range(Starting_Cell, ws.Cells(lastRow, lastCol))
    
    ' Sort the range alphabetically
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=rng.Columns(1), Order:=xlAscending, DataOption:=xlSortTextAsNumbers
        .SetRange rng
        .Header = xlNo
        .MatchCase = False
        .Apply
    End With
End Sub

I have attached the Macro-enabled Excel file. You can adjust the code according to your needs. Feel free to reach us if you need any further help.

Best Regards
Aniruddah
Team Exceldemy
 

Attachments

Online statistics

Members online
0
Guests online
14
Total visitors
14

Forum statistics

Threads
371
Messages
1,622
Members
703
Latest member
era
Back
Top