Forum Discussion

athegn's avatar
athegn
Copper Contributor
Jul 15, 2024

Macro to Sort two columns

I need an Excel Macro for sorting.

 

I have two columns with data. A contains addresses and B their grid reference. I add new addresses and their Grid references after the last currently used row. I then sort ascending, on column A. I have the following code from using Libre Office. what needs changing to run in Excel. I think I understand this code: On the Active sheet, select cell A3, then find the last used row in Column A. Now sort on column A. set range to A3:B(last Row in column a), do not sort row 3 as this is the header, accept any case, sort top to bottom. Do not need "SortMethod = xlPinYin" as only English characters; do I need another method. Lastly Apply sort. NOTE the macro will be run from a Push Button on the Grid_)references sheet.

 

Can anyone please advise?

 

I guess the Excel code needs:-

 

Select correct worksheet:      Sheets("Grid_References").Select

Select Range:     e.g Cell A3:B(Last used A Row): ActiveSheet.Range("a10000").End(xlUp).Row

Sort on column A:     Columns("A:B").Sort key1:=Range("A3:B Last A Row"), order1:=xlAscending, Header:=xlYes

 

Libre Offfice macro:-

 

RRem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1
'Sorting Named Range A-Column-Wise in Ascending Order
Sub Grid_References()

'## 24/11/2023 ##

Set ws = ActiveSheet

Dim rowOne, L

rowOne = 3 'data from row 3 / headings

L = ws.Cells(Rows.Count, "A").End(xlUp).Row

With ws.Sort

.SortFields.Clear

.SortFields.Add Key:=ws.Range(ws.Cells(rowOne, "A"), ws.Cells(L, "A")), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

.SetRange ws.Range(ws.Cells(rowOne, "A"), ws.Cells(L, "B"))

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

End Sub

  • athegn 

    That macro, from Sub Grid_References() to End Sub, should work without modification.

    You don't need the lines above Sub Grid_References().

Resources