Excel Macro - making a range dynamic for Sort and Autofilter functions

Melimob

Active Member
Joined
Oct 16, 2011
Messages
365
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>
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,402
Try something like this (not tested)

Code:
    [color=darkblue]Dim[/color] Lastrow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]With[/color] Worksheets("DATA Travel Destination Detail")
        
        .Sort.SortFields.Clear
            
        [color=green]'Last used row in column A[/color]
        Lastrow = Range("A" & Rows.Count).End(xlUp).Row
        
        .Sort.SortFields.Add _
            key:=Range("AA13:AA" & Lastrow), SortOn:=xlSortOnValues, _
            Order:=xlAscending, DataOption:=xlSortNormal
        
        .Sort.SortFields.Add _
            key:=Range("H13:H" & Lastrow), SortOn:=xlSortOnValues, _
            Order:=xlDescending, DataOption:=xlSortNormal
        
        .Sort
        .SetRange Range("A12:AA" & Lastrow)
        .Header = xlYes
        .MatchCase = [color=darkblue]False[/color]
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        
        .AutoFilterMode = [color=darkblue]False[/color]
        .Range("A12:AA" & Lastrow).AutoFilter Field:=27, Criteria1:="ASIA"
        
    [color=darkblue]End[/color] [color=darkblue]With[/color]
 

Melimob

Active Member
Joined
Oct 16, 2011
Messages
365
Hi AlphaFrog

thanks for your reply. You can probably tell I am a complete novice at VBA... I tried to enter your code but it's not working...highlights .SORT

it may be because I've entered it in the wrong place...

here's my full original code:

Sub Clean6()</SPAN>
'</SPAN>
' Clean6 Macro</SPAN>
' Copies entire sheet and paste special values to remove formulas. ' Formats entire sheet to Arial 10 ' Sorts by Destination Region and Travel Start Date ' Filters on one region at a time ' Copies data to relevant Region DETAIL tab</SPAN>
'</SPAN>

'</SPAN>
Cells.Select</SPAN>
Selection.Copy</SPAN>
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _</SPAN>
:=False, Transpose:=False</SPAN>
Application.CutCopyMode = False</SPAN>
With Selection.Font</SPAN>
.Name = "Arial"</SPAN>
.Size = 10</SPAN>
.Strikethrough = False</SPAN>
.Superscript = False</SPAN>
.Subscript = False</SPAN>
.OutlineFont = False</SPAN>
.Shadow = False</SPAN>
.Underline = xlUnderlineStyleNone</SPAN>
.ThemeColor = xlThemeColorLight1</SPAN>
.TintAndShade = 0</SPAN>
.ThemeFont = xlThemeFontMinor</SPAN>
End With</SPAN>
With Selection.Font</SPAN>
.Name = "Arial"</SPAN>
.Size = 10</SPAN>
.Strikethrough = False</SPAN>
.Superscript = False</SPAN>
.Subscript = False</SPAN>
.OutlineFont = False</SPAN>
.Shadow = False</SPAN>
.Underline = xlUnderlineStyleNone</SPAN>
.ThemeColor = xlThemeColorLight1</SPAN>
.TintAndShade = 0</SPAN>
.ThemeFont = xlThemeFontMinor</SPAN>
End With</SPAN>
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>
Range("A13").Select</SPAN>
Range(Selection, Selection.End(xlToRight)).Select</SPAN>
Range("A13:Z13").Select</SPAN>
Range(Selection, Selection.End(xlDown)).Select</SPAN>
Selection.SpecialCells(xlCellTypeVisible).Select</SPAN>
Selection.Copy</SPAN>
Sheets("ASIA DETAIL ").Select</SPAN>
Range("A19").Select</SPAN>
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _</SPAN>
:=False, Transpose:=False</SPAN>
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst</SPAN>
Sheets("DATA Travel Destination Detail").Select</SPAN>

Application.CutCopyMode = False</SPAN>
Range("N13").Select</SPAN>

ActiveSheet.Range("$A$12:$AA$15").AutoFilter Field:=27, Criteria1:="EMEA"</SPAN>

Range("A15").Select</SPAN>
Range(Selection, Selection.End(xlToRight)).Select</SPAN>
Range("A15:Z15").Select</SPAN>
Selection.Copy</SPAN>
ActiveWindow.ScrollWorkbookTabs Sheets:=1</SPAN>
Sheets("EMEA DETAIL").Select</SPAN>
Range("Table1[Origin Country]").Select</SPAN>
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _</SPAN>
:=False, Transpose:=False</SPAN>
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst</SPAN>
Sheets("DATA Travel Destination Detail").Select</SPAN>
Range("M23").Select</SPAN>
Application.CutCopyMode = False</SPAN>

Range("A17").Select</SPAN>
Selection.AutoFilter</SPAN>
Range("A13").Select</SPAN>
End Sub



It could probably be a lot cleaner but as I say, have very little knowledge with code so excuse my ignorance and thank you!.


Do you know where I should enter your code?

KR</SPAN>
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,402
Code:
[color=darkblue]Sub[/color] Clean6()
    [color=green]'[/color]
    [color=green]' Paste values to remove formulas[/color]
    [color=green]' Formats entire sheet to Arial 10[/color]
    [color=green]' Sorts by Destination Region and Travel Start Date[/color]
    [color=green]' Filters on one region at a time[/color]
    [color=green]' Copies data to relevant Region DETAIL tab[/color]
    [color=green]'[/color]
    
    [color=darkblue]Dim[/color] Lastrow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]With[/color] Worksheets("DATA Travel Destination Detail")
    
        [color=darkblue]If[/color] .FilterMode [color=darkblue]Then[/color] .ShowAllData
        
        [color=green]' Paste values to remove formulas[/color]
        .UsedRange.Value = .UsedRange.Value
        
         [color=green]'Last used row in column A[/color]
        Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
        
        [color=green]' Formats entire sheet to Arial 10[/color]
        [color=darkblue]With[/color] .Cells.Font
            .Name = "Arial"
            .Size = 10
            .Strikethrough = [color=darkblue]False[/color]
            .Superscript = [color=darkblue]False[/color]
            .Subscript = [color=darkblue]False[/color]
            .OutlineFont = [color=darkblue]False[/color]
            .Shadow = [color=darkblue]False[/color]
            .Underline = xlUnderlineStyleNone
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .ThemeFont = xlThemeFontMinor
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        
        [color=green]' Sorts by Destination Region and Travel Start Date[/color]
        [color=darkblue]With[/color] .Sort
            .SortFields.Clear
            .SortFields.Add _
                Key:=Range("AA13:AA" & Lastrow), SortOn:=xlSortOnValues, _
                Order:=xlAscending, DataOption:=xlSortNormal
            .SortFields.Add _
                Key:=Range("H13:H" & Lastrow), SortOn:=xlSortOnValues, _
                Order:=xlDescending, DataOption:=xlSortNormal
            .SetRange Range("A12:AA" & Lastrow)
            .Header = xlYes
            .MatchCase = [color=darkblue]False[/color]
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        
        [color=green]' Filters on one region at a time[/color]
        [color=green]' Copies data to relevant Region DETAIL tab[/color]
        .Range("A12:AA" & Lastrow).AutoFilter Field:=27, Criteria1:="ASIA"
        .Range("A13:Z" & Lastrow).Copy Destination:=Sheets("ASIA DETAIL ").Range("A19")
        
        .Range("A12:AA" & Lastrow).AutoFilter Field:=27, Criteria1:="EMEA"
        .Range("A13:Z" & Lastrow).Copy Destination:=Sheets("EMEA DETAIL").Range("Table1[Origin Country]")
        
        
        .AutoFilterMode = [color=darkblue]False[/color]
        
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,173
Members
417,129
Latest member
geekzilla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top