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

[Johnny C]

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)

        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!

 

[HaHoBe]

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