Hi there,
I've posted before about making dynamic ranges in recorded macros which has helped yet the same doesn't work for sort and autofilter functions.
I have a spreadsheet which each week will have more or less rows.
Also the data I am filtering each week will start on different rows.
SORT
I'm trying to sort all the data shown.
At the moment it only sorts upto Row 15 as below. How would I change this to look at whatever is the last row?
Range("A12").Select</SPAN>
ActiveWorkbook.Worksheets("DATA Travel Destination Detail").Sort.SortFields. _</SPAN>
Clear</SPAN>
ActiveWorkbook.Worksheets("DATA Travel Destination Detail").Sort.SortFields. _</SPAN>
Add Key:=Range("AA13:AA15"), SortOn:=xlSortOnValues, Order:=xlAscending, _</SPAN>
DataOption:=xlSortNormal</SPAN>
ActiveWorkbook.Worksheets("DATA Travel Destination Detail").Sort.SortFields. _</SPAN>
Add Key:=Range("H13:H15"), SortOn:=xlSortOnValues, Order:=xlDescending, _</SPAN>
DataOption:=xlSortNormal</SPAN>
With ActiveWorkbook.Worksheets("DATA Travel Destination Detail").Sort</SPAN>
.SetRange Range("A12:AA15")</SPAN>
.Header = xlYes</SPAN>
.MatchCase = False</SPAN>
.Orientation = xlTopToBottom</SPAN>
.SortMethod = xlPinYin</SPAN>
.Apply</SPAN>
End With</SPAN>
Selection.AutoFilter</SPAN>
ActiveSheet.Range("$A$12:$AA$15").AutoFilter Field:=27, Criteria1:="ASIA"</SPAN>
The next is I have an autofilter on
Row 12 is my header cells
I want to select visible data from row below A12 and to end row.
As this is an autofilter, sometimes the first row could be 15 sometimes it would be 13 etc..
Again, the number of rows will differ each week and will be more or less.
Many thanks - I feel hopeless at the moment, have been trying this for hours!
</SPAN>
I've posted before about making dynamic ranges in recorded macros which has helped yet the same doesn't work for sort and autofilter functions.
I have a spreadsheet which each week will have more or less rows.
Also the data I am filtering each week will start on different rows.
SORT
I'm trying to sort all the data shown.
At the moment it only sorts upto Row 15 as below. How would I change this to look at whatever is the last row?
Range("A12").Select</SPAN>
ActiveWorkbook.Worksheets("DATA Travel Destination Detail").Sort.SortFields. _</SPAN>
Clear</SPAN>
ActiveWorkbook.Worksheets("DATA Travel Destination Detail").Sort.SortFields. _</SPAN>
Add Key:=Range("AA13:AA15"), SortOn:=xlSortOnValues, Order:=xlAscending, _</SPAN>
DataOption:=xlSortNormal</SPAN>
ActiveWorkbook.Worksheets("DATA Travel Destination Detail").Sort.SortFields. _</SPAN>
Add Key:=Range("H13:H15"), SortOn:=xlSortOnValues, Order:=xlDescending, _</SPAN>
DataOption:=xlSortNormal</SPAN>
With ActiveWorkbook.Worksheets("DATA Travel Destination Detail").Sort</SPAN>
.SetRange Range("A12:AA15")</SPAN>
.Header = xlYes</SPAN>
.MatchCase = False</SPAN>
.Orientation = xlTopToBottom</SPAN>
.SortMethod = xlPinYin</SPAN>
.Apply</SPAN>
End With</SPAN>
Selection.AutoFilter</SPAN>
ActiveSheet.Range("$A$12:$AA$15").AutoFilter Field:=27, Criteria1:="ASIA"</SPAN>
The next is I have an autofilter on
Row 12 is my header cells
I want to select visible data from row below A12 and to end row.
As this is an autofilter, sometimes the first row could be 15 sometimes it would be 13 etc..
Again, the number of rows will differ each week and will be more or less.
Many thanks - I feel hopeless at the moment, have been trying this for hours!
</SPAN>