CODEWITHSUNDEEP
excelvbafiltermultiple-columnsmultiple-value
byte00
byte00

Reputation: 43

Filter by multiple values and multiple columns

I am trying to make a custom filtering solution within an Excel file, but I'm not sure if it is possible. I did some research on the Internet, and I came up with the solution from bellow, but when I run it gives me

run-time error '5': Invalid procedure call or argument.

Option Explicit

Sub Filtrare_date()

Dim Data_sh As Worksheet
Dim Raport_sh As Worksheet
Dim output_sh As Worksheet

Set Data_sh = ThisWorkbook.Sheets("Date")
Set Raport_sh = ThisWorkbook.Sheets("Raport")
Set output_sh = ThisWorkbook.Sheets("output")

output_sh.UsedRange.Clear
Data_sh.AutoFilterMode = False

'definim lista 1 de filtrare
Dim Filter_list() As String
Dim n As Integer

n = Application.WorksheetFunction.CountA(Raport_sh.Range("g:g")) - 2
ReDim Filter_list(n) As String

Dim i As Integer
For i = 0 To n
    Filter_list(i) = Raport_sh.Range("g" & i + 2)
Next i

'definim lista 2 de filtrare
Dim Filter_list_2() As String
Dim m As Integer

m = Application.WorksheetFunction.CountA(Raport_sh.Range("h:h")) - 2
ReDim Filter_list(m) As String

Dim j As Integer
For j = 0 To m
    Filter_list(j) = Raport_sh.Range("h" & j + 2)
Next j

'filtru dupa lista 1
Data_sh.UsedRange.AutoFilter 1, Filter_list(), xlFilterValues
'filtru dupa lista 2
Data_sh.UsedRange.AutoFilter 2, Filter_list_2(), xlFilterValues

Data_sh.UsedRange.SpecialCells(xlCellTypeVisible).Copy output_sh.Range("A1")
Data_sh.AutoFilterMode = False

MsgBox ("Selectia de date s-a terminat")

End Sub

The error is related to this line:

Data_sh.UsedRange.AutoFilter 2, Filter_list_2(), xlFilterValues

Upvotes: 0

Views: 201

Answers (2)

CDP1802
CDP1802

Reputation: 16174

The Worksheet Function transpose is a useful method of building selection criteria from a range without looping. Also by using With .. End With blocks you can avoid specifying the name of the object multiple times. For example

 Sub Filtrare_date()

    Dim wsData As Worksheet, wsRaport As Worksheet, wsOutput As Worksheet
    With ThisWorkbook
        Set wsData = .Sheets("Date")
        Set wsRaport = .Sheets("Raport")
        Set wsOutput = .Sheets("output")
    End With

    wsOutput.Cells.Clear
    wsData.AutoFilterMode = False

    Dim ar1 As Variant, ar2 As Variant, i As Long
    With wsRaport
        i = .Range("G" & Rows.Count).End(xlUp).Row
        ar1 = WorksheetFunction.Transpose(.Range("G3:G" & i).Value)
        i = .Range("H" & Rows.Count).End(xlUp).Row
        ar2 = WorksheetFunction.Transpose(.Range("H3:H" & i).Value)
    End With
    'Debug.Print Join(ar1, ","), Join(ar2, ","),

    With wsData.UsedRange
        .AutoFilter 1, ar1, xlFilterValues
        .AutoFilter 2, ar2, xlFilterValues
        .SpecialCells(xlCellTypeVisible).Copy wsOutput.Range("A1")
    End With
    wsData.AutoFilterMode = False

    MsgBox ("Selectia de date s-a terminat"), vbInformation

End Sub

Upvotes: 0

ZygD
ZygD

Reputation: 24366

The following part had some errors: 

m = Application.WorksheetFunction.CountA(Raport_sh.Range("h:h")) - 2
ReDim Filter_list_2(m) As String

Dim j As Integer
For j = 0 To m
    Filter_list_2(j) = Raport_sh.Range("h" & j + 2)
Next j

Upvotes: 1

Related Questions