VBA Macro: sorting in Column A

derricklo1980 · Aug 27th 2012

Hi ALL,
I want to write a macro that can sort the column A in whatever worksheets. I try to record the sorting and edit the code. But the problem I face is I don't know what to put inside the ListObjects(), any idea?

Code

Range("A2").Select
    ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table_owssvr_1279").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table_owssvr_1279").Sort. _
        SortFields.Add Key:=Range("A2"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets(wsName).ListObjects("Table_owssvr_1279").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Display More

Smallman · Aug 27th 2012

Re: VBA Macro: sorting in Column A

Hi Derrick

So you just want to sort Col A. Try this

Code

Sub Sort()
Range("A2", Range("A" & Rows.Count).End(xlUp)).Sort Columns(1), xlAscending
End Sub

Take care

Smallman

derricklo1980 · Aug 28th 2012

Re: VBA Macro: sorting in Column A

Hi Smallman,

Actually I want to keep Row 1 as the header row, i.e. the first row (A1) does not need to be sorted. How to cater this? Thanks!

derricklo1980 · Aug 28th 2012

Re: VBA Macro: sorting in Column A

as I found that if the worksheet only have 2 rows (e.g. A1=7, A2=2), the sorting will apply to the first row also ...

Smallman · Aug 28th 2012

Re: VBA Macro: sorting in Column A

Now I am truely confused. If you want to start in A1 then change the A2 in the code i provided to A1. Not tested but should work.

Smallman

derricklo1980 · Aug 29th 2012

Re: VBA Macro: sorting in Column A

Hi Smallman,
Actually I mean I will sort Column A but other columns' values should also be sorted accordingly. Any idea?

VBA Macro: sorting in Column A

Participate now!

Share