AutoFilter Percent Values +-5%

[Paul_Hossler]

FWIW, it seems to work OK here



Capture.JPG


Book1.xlsm is 'ThisWorkbook' with the macros and data. It's possible that using ActiveWorkbook didn't return the workbook that you thought it was

Book2.xlsx is the one with "F132:F146" values

Option Explicit


Sub StatTest()
    Dim wb1         As Workbook, wb2 As Workbook
    Dim ws1         As Worksheet, ws2 As Worksheet
    Dim rng         As Range, rng9   As Range
    Dim lr          As Long
    Dim MaxValue    As Double
    Dim MinValue    As Double
    
    
    Application.ScreenUpdating = False
    
    Set wb1 = ThisWorkbook
    Set ws1 = wb1.Sheets("Sheet1")
    
    Set wb2 = Workbooks("Book2.xlsx")
    Set ws2 = wb2.Sheets("Sheet1")
    
    lr = ws1.Cells(Rows.Count, "A").End(xlUp).Row
    
    Set rng = ws1.Range("A2:J" & lr)            'Made used range smaller for easier overview. Still same problem.
    Set rng9 = ws2.Range("F132:F146")
    
    rng9.NumberFormat = "0%"                    'To show and make sure rng9 and Col.F have same format.
    ws1.Range("F2:F" & lr).NumberFormat = "0%"  'To show and make sure rng9 and Col.F have same format.
    
    
    MaxValue = Application.WorksheetFunction.Max(rng9)
    MaxValue = MaxValue + 0.05
    MinValue = MaxValue - 0.1
    
    With ws1
        .AutoFilterMode = False
        .Range("A1:J1").AutoFilter
        rng.AutoFilter Field:=6, Criteria1:=">=" & MinValue, Operator:=xlAnd, Criteria2:="<=" & MaxValue    'Works for NumberFormat= numbers, not NumberFormat= %
    End With
    
    Application.ScreenUpdating = True
End Sub

Matter of personal style, but I'd rearrange the macro to group the workbook processes together

Option Explicit


Sub StatTest()
    Dim wb1         As Workbook, wb2 As Workbook
    Dim ws1         As Worksheet, ws2 As Worksheet
    Dim rng         As Range, rng9   As Range
    Dim lr          As Long
    Dim MaxValue    As Double
    Dim MinValue    As Double
    
    
    Application.ScreenUpdating = False
    
    Set wb1 = ThisWorkbook
    Set ws1 = wb1.Sheets("Sheet1")
    lr = ws1.Cells(Rows.Count, "A").End(xlUp).Row
    Set rng = ws1.Range("A2:J" & lr)            'Made used range smaller for easier overview. Still same problem.
    ws1.Range("F2:F" & lr).NumberFormat = "0%"  'To show and make sure rng9 and Col.F have same format.
    
    
    Set wb2 = Workbooks("Book2.xlsx")
    Set ws2 = wb2.Sheets("Sheet1")
    Set rng9 = ws2.Range("F132:F146")
    rng9.NumberFormat = "0%"                    'To show and make sure rng9 and Col.F have same format.
    
    
    MaxValue = Application.WorksheetFunction.Max(rng9)
    MaxValue = MaxValue + 0.05
    MinValue = MaxValue - 0.1
    
    With ws1
        .AutoFilterMode = False
        .Range("A1:J1").AutoFilter
        rng.AutoFilter Field:=6, Criteria1:=">=" & MinValue, Operator:=xlAnd, Criteria2:="<=" & MaxValue    'Works for NumberFormat= numbers, not NumberFormat= %
    End With
    
    Application.ScreenUpdating = True
End Sub