VBA Autofilter applying to whole set of data not specified range within it

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,069
Office Version
  1. 365
Platform
  1. Windows
I've got a workbook that has a block of data.
Headers, then a variable number of rows for the current month, then below the totals for prior months.
Not how I'd have done it but it's not my spreadsheet.

Anyway I want to filter and sort just the rows for the current month.

But when I try and apply it, it applies it to all the data not just the range I want. It does this when I do it manually as well, select the range I want the autofilter applied to but it applies it to the whole data set.

Can I just get it to apply to just the data I want? I know you usually want it but sometimes if you do it manually it only does it to some not the whole set so I'm sure it must be possible

Here's the code. IntLastRowCurr = 13 on this sheet, strCrit2 = -1000. But the autofilter applies it to 36 rows not just between rows 9 & 13 (Row 8 is headers for the autofilter)

VBA Code:
        Range(Cells(8, 1), Cells(IntLastRowCurr - 1, 9)).Select
       
        If strCrit1 <> "" And strCrit2 <> "" Then ActiveSheet.Range(Cells(8, 1), Cells(IntLastRowCurr - 1, 9)).AutoFilter Field:=9, Criteria1:=">" & strCrit1, Operator:=xlOr, Criteria2:="<" & strCrit2
        If strCrit1 <> "" And strCrit2 = "" Then ActiveSheet.Range(Cells(8, 1), Cells(IntLastRowCurr - 1, 9)).AutoFilter Field:=9, Criteria1:=">" & strCrit1
        If strCrit1 = "" And strCrit2 <> "" Then ActiveSheet.Range(Cells(8, 1), Cells(IntLastRowCurr - 1, 9)).AutoFilter Field:=9, Criteria1:="<" & strCrit2
       
        With ActiveWorkbook.ActiveSheet.AutoFilter.Sort
            .SortFields.Add2 Key:=Range(Cells(8, 9), Cells(IntLastRowCurr - 1)), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortTextAsNumbers
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

TIA for any help!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi Johnny C,

maybe it's time to update your profile to show what version of Excel you use.

For me Excel 2019 denies the sorting with a hint about the areas when it is one huge set. Would it be possible to add a blank row before and after the datarange of months, sort, delete the inserted rows and then AutoFilter?

Holger
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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