CODEWITHSUNDEEP
excelvbasortingfiltering
KSharp
KSharp

Reputation: 1

How to sort filtered data in vba?

I have a worksheet populated with data. I need to filter the data that could only show the info w/in 5 miles. Once the data is filtered to w/in 5 miles, I need to sort the variance column in ascending order. I used a record macro and attempted to incorporate it. The filtering works fine but I get an error saying:

Compile Error: Expected Array

when I run it. Here is a snippet of my code. When the error pops up, the Range is highlighted on the 6th line of code.

.Worksheets("Market Work").Cells.Select
Selection.AutoFilter
.Worksheets("Market Work").Range("$A$1:$Q$" & RowLast2).AutoFilter Field:=5, Criteria1:= _
    "w/in 5 miles"


ActiveWorkbook.Worksheets("Market Work").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Market Work").Sort.SortFields.Add Key:=Range( _
    "G2:G112"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("Market Work").Sort
    .SetRange Range("A1:Q112")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Upvotes: 0

Views: 13936

Answers (1)

Herry Markowitz
Herry Markowitz

Reputation: 308

Following code will help you...

Sheets("Finnet").Select
Sheets("Finnet").AutoFilterMode = False
Sheets("Finnet").Range("A1", Range("XFD1").End(xlToLeft)).Select
Sheets("Finnet").Range(Selection, Range("A" & Rows.Count).End(xlUp)).AutoFilter
Sheets("Finnet").AutoFilter.Sort.SortFields.Add Key:=Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlAscending
Sheets("Finnet").AutoFilter.Sort.Header = xlYes
Sheets("Finnet").AutoFilter.Sort.Apply
Sheets("Finnet").AutoFilterMode = False

Upvotes: 1

Related Questions