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

M

Melimob

Active Member
Joined
Oct 16, 2011
Messages
395
Office Version
  1. 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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
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]
 
Upvote 0
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>
 
Upvote 0
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]
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question
Code is missing Something
Replies
2
Views
185
farr80
F
C
  • Question
How to get my macro to stop at the last recognized line
Replies
6
Views
208
Coco1
C
O
  • Solved
VBA: Deleting Visible rows using Auto Filter; Excluding Header
Replies
4
Views
379
mumps
mumps
A
  • Solved
VBA - button to sort by date in ascending and descending order
Replies
6
Views
368
Peter_SSs
Peter_SSs
Dazzawm
  • Question
Change Recorded Range To Used Range.
2
Replies
12
Views
367
Dazzawm
Dazzawm

Forum statistics

Threads
1,214,920
Messages
6,122,264
Members
449,075
Latest member
staticfluids

Share this page

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