VBA creating dynamic sort list

[molsonlabatt]

Hi,

I have read a lot on this topic, but I am still stumped.

What I need is a dynamic range for both columns and rows (I.e. the number of columns and number of rows will change). The entire selection will need to be sorted by the last column with data.

Below is the VBA code I get from the recorder. I know I need to create a dynamic range for but, I am unable to get it to work. I have highlighted below the areas that need to change (I think).

Cheers


Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("H1:H20") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:H20")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

 

[molsonlabatt]

SOLVED

lastRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
LastCol = ThisWorkbook.Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
' Gets last row number and last column number

Set SortA = ActiveSheet.Range(Cells(1, 1), Cells(lastRow, LastCol))
' Create name for all sort area

Range("A1").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Worksheets("Sheet1").Cells(1, LastCol), Worksheets("Sheet1").Cells(lastRow, LastCol)), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange SortA
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' Sort cells by last column

 

[sinned141]

Thanks for posting the solution, I was watching this thread and hoping someone would answer as I had a similar problem.