Sort排序

Question:Sort Dynamic Range

Issue:
1) I have a dynamic data range, (i.e. # of rows and cols may vary).
2) example range: A4:D10
3) Problem: the sort function is static and not dynamic, I tried to give
it a name range but I get an error.

Current Macro:

Sub test1()
'
' test1 Macro
'

'
ActiveWorkbook.Names("sortrange").Delete
Application.Goto Reference:="client1"
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add Name:="sortrange", RefersToR1C1:= _
ActiveWindow.RangeSelection.Address
' "=Sheet1!R4C1:R8C4"
ActiveWorkbook.Names("sortrange").Comment = ""
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=ActiveCell, _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange ActiveCell.Range("sortrange") <===ISSUE: needs to be
dynamic
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.Goto Reference:="R1C1"
End Sub

 

Answer

Can you use a column in your data to determine the last row to sort? Can you use a row in your data to determine the last column to sort? Dim LastRow As Long Dim LastCol As Long With ActiveSheet 'I'm using column A to determine the last row LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'and row 4 to determine the last column LastCol = .Cells(4, .Columns.Count).End(xlToLeft).Row With .Range("A4", .Cells(LastRow, LastCol)) .Cells.Sort _ Key1:=.Columns(1), _ Order1:=xlAscending, _ Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortTextAsNumbers End With End With ======= I sorted by the first column (column A) and I guessed that you had a header. It's your data. It's better if you specify it than let excel guess.