PDA

View Full Version : [SOLVED] Data sort a dynamic range?



roxnoxsox
03-15-2016, 07:28 AM
I'm trying to write a macro which will data sort a dynamic range. The number of columns will always be the same (A:N) but the number of rows will change.

So I want to start at row 4 and sort all data from here down, based on the contents of column N. I don't have any headers.

Here is what I managed to record but I'm not sure how this should be changed to fit my range?



Sub TestMacro()


Rows("4:274").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("N4:N274") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A4:N274")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub



Many thanks in advance

Bob Phillips
03-15-2016, 08:17 AM
Sub TestMacro()
Dim lastrow As Long

With ActiveWorkbook.Worksheets("Sheet3")


lastrow = .Cells(.Rows.Count, "N").End(xlUp).Row
.Sort.SortFields.Clear

.Sort.SortFields.Add Key:=.Range("N4").Resize(lastrow - 3), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
.Sort.SetRange .Range("A4:N4").Resize(lastrow - 3)
.Sort.Header = xlNo
.Sort.MatchCase = False
.Sort.Orientation = xlTopToBottom
.Sort.SortMethod = xlPinYin
.Sort.Apply
End With
End Sub

roxnoxsox
03-30-2016, 05:09 AM
Perfect, thank you!