Make a macro include whatever number of rows/columns when sorting out

Hi all,

I recorded a macro to sort out data in reverse order, the only problem I still have, is to make it interactive so it includes and sorts as many columns as the sheet might have. Data can be in just column A, or take as many columns as going to AG, with an always changing number of rows.

Below is the part of the code I need help with (where I set up the range to Z500 in the meantime), I'd like the macro to include whatever number of rows and columns. I'll appreciate any help!

ActiveSheet.SORT.SortFields.Clear
ActiveSheet.SORT.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

With ActiveSheet.SORT
.SetRange Range("A1:Z500")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

Dim lrow, lcol as integer 'unless a lot, then use long
   lrow = activesheet.cells(rows.count, 1).end(xlup).row
   lcol = activesheet.cells(1, columns.count).end(xltoleft).column

This will set lrow to the last row of data in column 1 and set lcol to the last column of data in row 1.

You can change the row/col by changing what cell(x, y) address you reference.

To get an overall last row and column on a sheet you can instead use:

lrow = activesheet.UsedRange.Rows.Count
lcol = activesheet.UsedRange.Columns.Count

Once you have the boundaries defined you can monkey with the ranged references in the sort macro using cell address instead of "A1" references. It takes a bit of poking to adjust the syntax just right but it's doable.

woops forgot to do the columns too
So you can have it in a letter syntax I added a function.

Function Col_Letter(lngCol As Long) As String
Dim vArr
vArr = Split(Cells(1, lngCol).Address(True, False), "$")
Col_Letter = vArr(0)
End Function

Sub ORDER()
'
'ORDER Macro
'

Dim lrow As Integer 'unless a lot, then use long
Dim lcol As String 
   lrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
   lcol = Col_Letter(ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column)

    Range("A1:" & lcol & "1").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("A1:A" & lrow), SortOn:=xlSortOnValues, ORDER:=xlDescending, DataOption:=xlSortNormal
With ActiveSheet.Sort
        .SetRange Range("A1:" & lcol & lrow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
    End With
End Sub

Something like this (Following from what Ouka said above)

Sub ORDER()
'
'ORDER Macro
'

Dim lrow, lcol as integer 'unless a lot, then use long
   lrow = activesheet.cells(rows.count, 1).end(xlup).row
   lcol = activesheet.cells(1, columns.count).end(xltoleft).column

    Range("A1:Z1").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.SORT.SortFields.Clear
    ActiveSheet.SORT.SortFields.Add Key:=Range("A1:A" & Lrow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveSheet.SORT
        .SetRange Range("A1:Z" & Lrow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
    End With
End Sub

Huh, not bad. Never thought to grab the column letter like that.
I've always screwed around with .Cells(x, y).resize(lrow - x + 1, lcol - y + 1) to set my selection for sorting.

I'll have to steal that, thanks!

First of all,

THANK YOU guys so much for the help. I should have stated I'm still learning my way with VBA. I added the last code I was suggested, but guess I'm missing something, 'cause I couldn't make it work.

I think explaining more the scenario I'm working with would help. I process spreadsheets where the order of rows won't follow any function. Sometimes items can be ordered by visual attributes (like size) or in the order in which an item was received by my dept (such info is NOT included within the spreadsheet). Order changes with each order I process. The point is that, when I have the spreadsheet complete, I need to put all SKUs (column A), in reverse order to then proceed to print labels for all items. What I've been doing, is: a) Inserting a new column A. b)In cell A2, formula =ROW(A2)-1, c)Filling down all rows with the formula. d)Sort by column A, "largest to smallest" and last, e)Selecting column A and delete it. As mentioned before, sometimes my spreadsheet can have a lot of columns, sometimes can be just column A, but rows are always different. Below is the complete current code I have for the entire macro (This was mostly recorded, not typed in, since I'm still new to coding!):

' REVERSE Macro

Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A2").Select
Selection.FormulaR1C1 = "=ROW(RC)-1"
Selection.Copy
Range("B2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Columns("A:A").EntireColumn.Select

Application.CutCopyMode = False
ActiveSheet.SORT.SortFields.Clear
ActiveSheet.SORT.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveSheet.SORT
.SetRange Range("A1:M500")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Columns("A:A").Select
Selection.Delete Shift:=xlToRight
Range("A2").Select


End Sub