Macro to sort block of data

[henryg]

I regularly need to sort a block of data in reverse order - the columns are always the same but the number of rows changes as does the name of the workbook; always csv in my case. I have tried recording a macro but it is not generic, hard coding the name of the workbook and the size of the data block. I tried OpenAI but its solution fails every time.

The steps I use manually, starting at A1, are: autofit column A; create table with headers; goto F2 and add =Row(); select all data in block excluding headers by shift-end-down arrow (xlDown) & ctrl-shift- left arrow (xlToLeft); sort Z to A (cursor has remained in F2). Simple.

Recording a macro gives

Sub Macro4()
'
' Macro4 Macro
'

'
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$e$14"), , xlYes).Name = _
"Table1"
Range("F2").Select
Selection.FormulaR1C1 = "=ROW()"
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Columns("A:A").EntireColumn.AutoFit
ActiveWorkbook.Worksheets("GB").ListObjects( _
"Table1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("GB").ListObjects( _
"Table1").Sort.SortFields.Add2 Key:=Range("Table1[Column2]"), SortOn:= _
xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("GB").ListObjects( _
"Table1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

with the hard-coded name and range, while OpenAI gave me (which fails)

Sub DynamicSortMacro()
'
' DynamicSortMacro Macro
'

'
Dim ws As Worksheet
Dim tbl As ListObject
Dim lastRow As Long

' Set the worksheet where you want to apply the macro
Set ws = ThisWorkbook.Worksheets("Your_Worksheet_Name")

' Find the last row in the data block
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

' Add a ListObject (Table) to the specified range
Set tbl = ws.ListObjects.Add(xlSrcRange, ws.Range("$A$1:$E$" & lastRow), , xlYes)
tbl.Name = "Table1"

ws.Range("G2").FormulaR1C1 = "=ROW()"
ws.Range(ws.Range("G2"), ws.Range("G2").End(xlDown).End(xlToLeft)).Columns.AutoFit

' Apply sorting
With tbl.Sort.SortFields
.Clear
.Add2 Key:=tbl.ListColumns("Column2").Range, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
End With

With tbl.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

which gives "subscript out of range at "Set ws = ThisWorkbook.Worksheets("Your_Worksheet_Name")" which I can't get past.

Any help would be much appreciated.

[edit] it would be useful if the number of columns was non-specific too!

 

[Joe4]

Can you please show us an example of what this data looks like before you do anything to it, and what the expected result should look like?
Data structure and what columns are populated and which may have blanks could be important factors, so it would be good to get a visual picture of it.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Alex Blakenburg]

See if this gets you close to where you need to be.

Sub TestSort()

    Dim ws As Worksheet
    Dim tbl As ListObject, rng As Range
    Dim lastRow As Long, lastCol As Long

    Set ws = Worksheets("GB")
    With ws
        lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        Set rng = .Range("A1", .Cells(lastRow, lastCol))
    
        Set tbl = .ListObjects.Add(xlSrcRange, rng, , xlYes)
        tbl.Name = "Table1"
        .Range("F2").FormulaR1C1 = "=ROW()"
    End With
    
    With tbl
        .Range.Columns.AutoFit
        With .Sort
            .SortFields.Clear
            .SortFields.Add2 Key:=tbl.ListColumns(2).Range, SortOn:= _
                xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply            
        End With
    End With

End Sub

 

[Joe4]

I wonder if you may be able to just use CurrentRegion to make setting the range easier, i.e.
replacing all this:

        lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        Set rng = .Range("A1", .Cells(lastRow, lastCol))

with just this:

        Set rng = .Range("A1").CurrentRegion

It was one of the reasons why I wanted to see what the data looked like, to confirm whether or not we can use CurrentRegion.

 

[Alex Blakenburg]

Agreed, that would simplify it.
My other question was going to be whether the =Rows() was just numbering the rows or whether it was meant to capture the original order in which case it needs to be replaced with the value prior to the sort.

 

[henryg]

Agreed, that would simplify it.
My other question was going to be whether the =Rows() was just numbering the rows or whether it was meant to capture the original order in which case it needs to be replaced with the value prior to the sort.

Just numbering the rows to enable a reverse sort.

 

[henryg]

Can you please show us an example of what this data looks like before you do anything to it, and what the expected result should look like?
Data structure and what columns are populated and which may have blanks could be important factors, so it would be good to get a visual picture of it.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I didn't think it would help to upload any data, sorry. It's a contiguous block of data (content not relevant) with no blanks (I can see now that might be relevant) and I just want to reverse the order of each row in the block; no other data manipulation or changes.

 

[Joe4]

OK, I made the follow amendments to the code Alex came up with. See if this does what you want:

Sub TestSort()

    Dim ws As Worksheet
    Dim tbl As ListObject, rng As Range
    Dim lastRow As Long, lastCol As Long

    Set ws = Worksheets("GB")
    With ws
        lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
'       Populate column F with counter and hard-code
        .Range("F1") = "Sort"
        .Range("F2:F" & lastRow).FormulaR1C1 = "=ROW()"
        .Range("F2:F" & lastRow).Value = .Range("F2:F" & lastRow).Value
       
        Set rng = .Range("A1").CurrentRegion.Resize(, 6)
        Set tbl = .ListObjects.Add(xlSrcRange, rng, , xlYes)
        tbl.Name = "Table1"
    End With
   
    With tbl
        .Range.Columns.AutoFit
        With .Sort
            .SortFields.Clear
            .SortFields.Add2 Key:=tbl.ListColumns(6).Range, SortOn:= _
                xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With

'   If you wish to delete column F at end, uncomment following line:
'   Columns("F:F").Delete Shift:=xlToLeft

End Sub

 

[henryg]

I wonder if you may be able to just use CurrentRegion to make setting the range easier, i.e.
replacing all this:

        lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        Set rng = .Range("A1", .Cells(lastRow, lastCol))

with just this:

        Set rng = .Range("A1").CurrentRegion

It was one of the reasons why I wanted to see what the data looked like, to confirm whether or not we can use CurrentRegion.

When I made this change the macro didn't seem to work.

 

[Joe4]

When I made this change the macro didn't seem to work.

It worked just fine for me.

So maybe we really do need to see your data...

Is any of your data formulas?
Sorting formulas probably will change the sort order.