JohnnyMoney
New Member
- Joined
- Jul 8, 2017
- Messages
- 8
Hi all,
I have a very useful macro for filtering which lets me select a cell and filter a table based on that selected cell value, i.e., if cell B10 = 10, selecting cell B10 and running the macro filters that column for all cells containing exactly value 10.
In recent time, I often find my self wanting to filter values a) equal or greater than or b) equal or less than a selected cell value. This is done manually by typing it into Excel.
It would be great to have this done using a macro instead. I'd need two then (one for greater and one for less than).
Can my current macro be modified to accomplish this?
I'm pasting it below.
Thanks in advance for any pointers.
I have a very useful macro for filtering which lets me select a cell and filter a table based on that selected cell value, i.e., if cell B10 = 10, selecting cell B10 and running the macro filters that column for all cells containing exactly value 10.
In recent time, I often find my self wanting to filter values a) equal or greater than or b) equal or less than a selected cell value. This is done manually by typing it into Excel.
It would be great to have this done using a macro instead. I'd need two then (one for greater and one for less than).
Can my current macro be modified to accomplish this?
I'm pasting it below.
Thanks in advance for any pointers.
Code:
Sub CombinationFilter()
Dim cell As Range, tableObj As ListObject, subSelection As Range
Dim filterCriteria() As String, filterFields() As Integer
Dim i As Integer
'If the selection is in a table and one row height
If Not Selection.ListObject Is Nothing And Selection.Rows.Count = 1 Then
Set tableObj = ActiveSheet.ListObjects(Selection.ListObject.Name)
i = 1
ReDim filterCriteria(1 To Selection.Cells.Count) As String
ReDim filterFields(1 To Selection.Cells.Count) As Integer
' handle multi-selects
For Each subSelection In Selection.Areas
For Each cell In subSelection
filterCriteria(i) = cell.Text
filterFields(i) = cell.Column - tableObj.Range.Cells(1, 1).Column + 1
i = i + 1
Next cell
Next subSelection
With tableObj.Range
For i = 1 To UBound(filterCriteria)
.AutoFilter Field:=filterFields(i), Criteria1:=filterCriteria(i)
Next i
End With
Set tableObj = Nothing
End If
End Sub
