VBA - Filter based on cell values

[perrush]

Hi all,

First of all, let me say tha this forum already helpt me a lot in building my own excel sheets. Having said that, I don't see myself as an experienced user. I just manage to make my own macro's with the help of the record function. So far, they work and that enough for me.

Now I want to make a filter based on 2 values in a cell. The current filter is this :

Sheets("LTSB").Select
ActiveSheet.ListObjects("Tabel1").Range.AutoFilter Field:=28, Criteria1:= _
">-5", Operator:=xlAnd, Criteria2:="<10"

where >-5 and <10 are the two criteria to filter on.

I would like to have a filter where a user can put those 2 criteria in, lets say cell A1 and A2 and then run the macro again to get a different range.

Is there a simple way to achieve this ?

Stefan

 

[JLGWhiz]

the 'Select' is not necessary. This uses two variables to capture the cell values. You can change the cells to whatever you like, A1 and B1 are for illustration only.

Dim a As Long, b As Long
a = Sheets("LTSB").Range("A1").Value 'Change to cell of your choice
b = Sheets("LTSB").Range("B1").Value 'Change to cell of your choice
Sheets("LTSB").ListObjects("Tabel1").Range.AutoFilter Field:=28, Criteria1:= _
 a, Operator:=xlAnd, Criteria2:=b

God Yul

 

[perrush]

Hi JLGWhiz,

The 'select' comes from the record function. Because my VBA skills only reach to record -> copy -> paste such things happen. tnx for the input.

The code doesn't work yet. I suppose because I use a greater then / smaller then criteria which isn't defined in your code. How do I need to include them ? So I just want to put "-5" in cell A1 and the code should filter on 'greater then -5'

Stefan

 

[JLGWhiz]

My error, try this

Dim a As Long, b As Long
a = Sheets("LTSB").Range("A1").Value 'Change to cell of your choice
b = Sheets("LTSB").Range("B1").Value 'Change to cell of your choice
Sheets("LTSB").ListObjects("Tabel1").Range.AutoFilter Field:=28, Criteria1:= _
 [COLOR=#FF0000]">" &[/COLOR] a, Operator:=xlAnd, Criteria2:=[COLOR=#FF0000]"<" &[/COLOR] b

 

[perrush]

Great, works perfect. tnx

 

[JLGWhiz]

Great, works perfect. tnx

You're welcome,
regards, JLG

 

[perrush]

Hi all,

I've run into a problem when I save the sheet.

Previously the sheet was formatted as a table. In order to improve performance (applying a simple filter took 15 sec) I removed the tableformatting. Therefore I adjusted the code for the filter to :

    Dim a As Long, b As Long    a = Sheets("LTSB").Range("Z2").Value
    b = Sheets("LTSB").Range("Z3").Value
    
    Sheets("LTSB").Range("$A$7:$AE$5000").AutoFilter Field:=31, Criteria1:= _
     ">" & a, Operator:=xlAnd, Criteria2:="<" & b

Seems to work fine until I save the sheet and open it again. Then I get an error referring sorting/filtering to sheet3.xml. But I don't have a sheet3. The excel sheet repairs itself, without any faults, but this is ofcours not what you want.

With the table formatting I had the fault too. But then I added the following and the issues were solved.
(found in https://www.excelforum.com/excel-general/659777-excel-2007-sorting-message-re-removed-records-2.html)

ActiveWorkbook.Worksheets("LTSB").Sort.SortFields.Clear

it seems this code doesn't work anymore.

If I apply a manual filter and save the file. I don't get this error. So the error seems related to the way of filtering by VBA. Any solutions ?

--

Just to make it clear. This is the filter I want to apply. -76 and -62 are calculated values. Want to have a automated filter, I don't want the user to put in the values manually

 

[perrush]

Hi,

it seems that this error doesn't come form the filtering, but rather from the sorting.

But why does it happen only when I apply the filter by VBA and not manually ? If I leave on the sorting, but remove the filtering and then save. I don't get the error. If I apply the filtering manually I don't get the error either. I'm lost

 

[perrush]

sorry for the multiple posts but can't edit my previous post anymore.

underneath the error messages :

And finally one of the sorting codes :

' sort on Factory (AB), then product_ID (O), then VerID (Q), then inzetdatum (U) and finally on inzetuur (V)    ActiveWorkbook.Worksheets("LTSB").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "AB8:AB5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    
    ActiveWorkbook.Worksheets("LTSB").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "O8:O5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    
    ActiveWorkbook.Worksheets("LTSB").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "Q8:Q5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    
    ActiveWorkbook.Worksheets("LTSB").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "U8:U5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    
    ActiveWorkbook.Worksheets("LTSB").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "V8:V5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    
    With ActiveWorkbook.Worksheets("LTSB").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
' to avoid error after saving.
    ActiveWorkbook.Worksheets("LTSB").Sort.SortFields.Clear

 

[perrush]

Hi All,

played all day with the sheet (not only with this error) and suddenly the errors were gone. I think I needed to place the code at the beginning of the function and not at the end. Anyhow, problem solved