Zaigham
Board Regular
- Joined
- Dec 22, 2010
- Messages
- 159
- Office Version
- 2021
- Platform
- Windows
- Mobile
Hi,
I have recorded a macro in excel to sort data in a table. The rows of data vary each time. Sometimes it is less or sometimes it is more. Below is the part of code in which the range has been hard-coded. Please guide me to a way to get rid of this "Range("B4:L1098").Select" to be adjusted according to data volume.
"Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
Range("B4:L1098").Select
ActiveWorkbook.Worksheets("CurntPenExp").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("CurntPenExp").Sort.SortFields.Add2 Key:=Range( _
"L5:L1098"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("CurntPenExp").Sort.SortFields.Add2 Key:=Range( _
"D5:D1098"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("CurntPenExp").Sort
.SetRange Range("B4:L1098)
Regards
Zaigham
I have recorded a macro in excel to sort data in a table. The rows of data vary each time. Sometimes it is less or sometimes it is more. Below is the part of code in which the range has been hard-coded. Please guide me to a way to get rid of this "Range("B4:L1098").Select" to be adjusted according to data volume.
"Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
Range("B4:L1098").Select
ActiveWorkbook.Worksheets("CurntPenExp").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("CurntPenExp").Sort.SortFields.Add2 Key:=Range( _
"L5:L1098"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("CurntPenExp").Sort.SortFields.Add2 Key:=Range( _
"D5:D1098"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("CurntPenExp").Sort
.SetRange Range("B4:L1098)
Regards
Zaigham