PDA

View Full Version : Range setting for sort



bobko123
08-28-2018, 10:06 PM
Hello, I have an issue with range setting for sort filter.
I used macro recording and changed the range to make it variable.
I want to sort column A ascending from A4 to column V, the final row (variable).
Could anyone check the code and tell me what is wrong with it?
Thank you in advance!

*Version of the program: Windows 7 Professional, Excel 2010


***Code follows***



'集計シートをソートする
With ActiveWorkbook.Worksheets("集計").Sort

'フィルターを解除
.SortFields.Clear

'Entity(Ledger)の空白セルを除外
Range("A4").CurrentRegion.AutoFilter field:=6, _
Criteria1:="<>"

'NO1を昇順で並べ替え
Dim i
i = Cells(Rows.Count, 1).End(xlUp).Row

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("集計").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("集計").Sort.SortFields. _
Add Key:=Range("A4:cells(4,1).cells(i,22)"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

ActiveWorkbook.Worksheets("集計").Sort
.SetRange Cells(4, 1).Cells(Rows.Count, 1).End(xlUp).Row
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With

jolivanes
08-29-2018, 10:44 AM
Please use code tags when posting code.
Try this on a copy of your workbook.

Sub Sort_Bobko123()
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("A4:V" & lr).Sort Key1:=Range("A2"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

bobko123
08-31-2018, 02:06 AM
Thank you very much for the code and advice!:clap: