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
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