VBA Macro code for automatic arrangement

[nmz1133]

What is the VBA Macro code for arranging cells based on alphabetical order in excel. Given that the rows are increasing frequently and i want it to be automated

Thanks in advance for your help.

 

[Aniruddah Alam]

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:


The VBA code that I have used is given below:

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