Sub Filter_Sheet2(clickedRowNumber As Long)
Dim lastUsedColumnNumber As Integer, lastRowNumber As Long
With ThisWorkbook.Sheets("Sheet1")
lastUsedColumnNumber = .UsedRange.Columns.Count + .UsedRange.column - 1
End With
With ThisWorkbook.Sheets("Sheet2")
lastRowNumber = .Cells(.Rows.Count, 1).End(xlUp).row
.Range("1:1").AutoFilter
Dim column As Integer, filterExp As String, allFilterExpressions As String
allFilterExpressions = ""
If (clickedRowNumber >= 2) And (clickedRowNumber <= 4) Then
For column = 2 To lastUsedColumnNumber
filterExp = ThisWorkbook.Sheets("Sheet1").Cells(clickedRowNumber, column).Value
If Trim(filterExp) <> "" Then allFilterExpressions = allFilterExpressions & "," & filterExp
Next column
allFilterExpressions = Remove_String_Duplicates(Right(allFilterExpressions, Len(allFilterExpressions) - 1), ",")
.Range("A1:B" & lastRowNumber).AutoFilter Field:=.Range("A1").column, Criteria1:="<>"
.Range("A1:B" & lastRowNumber).AutoFilter Field:=.Range("A1").column, Criteria1:=Array(Split(allFilterExpressions, ",")), Operator:=xlFilterValues
Exit Sub
End If
If clickedRowNumber = 5 Then
For column = 2 To lastUsedColumnNumber
filterExp = ThisWorkbook.Sheets("Sheet1").Cells(clickedRowNumber, column).Value
If Trim(filterExp) <> "" Then allFilterExpressions = allFilterExpressions & "," & filterExp
Next column
allFilterExpressions = Remove_String_Duplicates(Right(allFilterExpressions, Len(allFilterExpressions) - 1), ",")
.Range("A1:B" & lastRowNumber).AutoFilter Field:=.Range("B1").column, Criteria1:="<>"
.Range("A1:B" & lastRowNumber).AutoFilter Field:=.Range("B1").column, Criteria1:=Array(Split(allFilterExpressions, ",")), Operator:=xlFilterValues
End If
End With
End Sub
Sub Unfilter_Sheet2()
ThisWorkbook.Sheets("Sheet2").AutoFilterMode = False
End Sub
Sub Test__Remove_String_Duplicates()
Debug.Print Remove_String_Duplicates("Red,Red,Blue", ",")
End Sub
Function Remove_String_Duplicates(stringArray As String, delimiter As String)
'https://www.excelhow.net/how-to-remove-duplicates-in-one-cell-in-excel.html
'If there is no commas, there is only one entry. And if there is only one entry, there is no duplicates.
If InStr(stringArray, delimiter) = 0 Then Remove_String_Duplicates = stringArray
Dim args() As String
args = Split(stringArray, delimiter)
Dim result As String
result = ""
Dim i As Long, j As Long, b As Boolean
For i = LBound(args) To UBound(args)
b = False
For j = i + 1 To UBound(args)
If StrComp(args(i), args(j), vbBinaryCompare) = 0 Then
b = True
Exit For
End If
Next j
If b = False Then result = result & delimiter & args(i)
Next i
Remove_String_Duplicates = Right(result, Len(result) - 1)
End Function