Forum Discussion

Kalogeros's avatar
Kalogeros
Copper Contributor
Aug 18, 2022

Ecxel VBA

Hi, i would like to filter a table (5000+ rows, 20+ columns) with multiple criteria. More specifically, i would like to give a unique id ( 6digit NUMBER from the first column) and return ALL the oth...
excel
Formulas and Functions
Macros and VBA
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Aug 18, 2022

Kalogeros 

VBA Code...

Sub AutofilterInput()
Dim Criterion1 As String, Range As Range
Set Range = ActiveSheet.UsedRange ' Adjust range accordingly
'Filter searches for "contains search text"
Criterion1 = "=*" & InputBox("Text you're looking for?", "Autofilter search term", "Search text") & "*"
If Criterion1 = "=**" Then Exit Sub
Range.AutoFilter Field:=1, Criteria1:=Kriterium1 'If several columns have an auto filter, adjust the field number if necessary
end sub

 

...or

Filter by using advanced criteria

 

 

Hope I could help you with these information / links.

 

NikolinoDE

I know I don't know anything (Socrates)

 

Kalogeros's avatar
Kalogeros
Copper Contributor
Aug 30, 2022

NikolinoDE  Thanks for the reply, but it doesnt work for me.

I want to replicate the FILTER function in Vba for Excel 2016-2019, as shown in the example below.

If you have any questions please contact me.

 

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Aug 31, 2022
    Please insert the file (without sensitive data) to have a clear view of the whole thing.
    At the same time, please describe your plans step by step.
    It would be advantageous to know the Excel version (e.g. 2016,16.0.5356.1001), operating system (win, mac, etc.) and storage medium (Sharepoint, OneDrive, hard drive, etc.).

    Thank you for your understanding and patience
    • Kalogeros's avatar
      Kalogeros
      Copper Contributor
      Aug 31, 2022

      NikolinoDE 

      I can't insert the actual file, because it contains too many sensitive data.
      However, it is much close to the example i gave you above.
      The plan is to give a value (id as in the example), then the script should see the values of the other fields of that particular id and return a table filtered with those field as criteria. Exactly like the FILTER function in the given example.

      The Excel version is 2016 and 2019 (can't remember the exact version) running on windows 10. The file used is stored in hard drive.

Resources