FilterSheet_1By1 + FilterSheet_Clear

Sets Autofilter in a sheet, and another function to clear Autofilter
FilterSheet_1By1 will set filter in a sheet for a certain table in a sheet.
Table means any list of cells (columns and rows) that identified by having no break
You can set the cell where that filter to be created (any cell within that Table)
This one will filter table by 1 value in 1 column
We are planning to have in future a filter by more than 1 value in more than 1 column.


Public

Tested

My Own Work
Sub FilterSheet_1By1(FilterByValue, Optional FilterColumn = "B", Optional Wb = "This", Optional Shee = "This", Optional FilterA1 = "A1")
' Filter a sheet in certain column using certain value
' Filter table in 1 column by 1 value
' FilterByValue is the value you want to filter by
' Accepts wild cards * and ?
' FilterColumn is the column name where to filter FilterByValue
' FilterA1 is cell address where we are expecting to do our AutoFilter
' FilterColumn is expected to be within same Region of FilterA1.
' Means FilterByColumn has to be in the same table as FilterA1
' No break (blank column) should be between FilterA1 and FilterColumn
'
If Wb = "This" Then Wb = ThisWorkbook.Name
If Shee = "This" Then Shee = Workbooks(Wb).Worksheets(1).Name
FilterSheet_Clear Wb, Shee
FilCol = Range(FilterColumn & 1).column
DoEvents
Workbooks(Wb).Worksheets(Shee).Range(FilterA1).AutoFilter FilCol, "=" & FilterByValue
DoEvents
End Sub
Sub FilterSheet_Clear(Optional Wb = "This", Optional Shee = "This")
' Clears Autofilter from a sheet
' Excel accepts 1 AutoFilter per sheet, so it doesn't matter where your filter is located, this call will remove it
' Tested in Excel2010
If Wb = "This" Then Wb = ThisWorkbook.Name
If Shee = "This" Then Shee = Workbooks(Wb).Worksheets(1).Name
Workbooks(Wb).Worksheets(Shee).AutoFilterMode = False
End Sub

FilterByValue, Optional FilterColumn = "B", Optional Wb = "This", Optional Shee = "This", Optional FilterA1 = "A1"
Or
Optional Wb = "This", Optional Shee = "This"

Views 111 Downloads 30

VBA-Excel Sessions
ANmarAmdeen
674
Attachments
Revisions

v1.0