Technology Programming

How To Hide And Unhide Rows With Your Own VBA Filter In Excel

While the tools in Excel for filtering data are good, they can be a little tricky to use and it might be worthwhile exploring a VBA solution for extracting the information you need from your spreadsheet.
In this article, we'll create a code snippet that hides data and toggles the filter on and off.
While focused on a simple scenario the code could be enhanced to create your own filtering tool.
Creating A Simple Toggle Filter With VBA The scenario we'll look at is simply to extract all the rows in a table that contain a certain customer name.

Name, Invoice# ABC Ltd,123 Johns Company,124 ABC Ltd,234 DEF Ltd,345 ABC Ltd,432 We'd just like the user to select a customer name in the table and the code should hide other rows not containing the name.
If the code is run again the filter should be removed.
When the user clicks on a name to search for the code ensures the selected cell is in column 1:
Sheets(1).
Activate searchfor = ActiveCell.
Value If ActiveCell.
Column <> 1 Then MsgBox "Please select a cell in column 1" Exit Sub End If Next, we select the column to search and loop through the data to find the search string:
Range("a1").
CurrentRegion.
Columns(1).
Select For x = 2 To Selection.
Count Now we have to determine whether or not the filter is in place.
If the cell does not match the search text AND the row is hidden, then the filter is in place.
Therefore, we make visible all the rows and exit the routine.
If the cell does is not a match AND the row is visible, then we hide the row and continue filtering the data.

If InStr(Selection(x), searchfor) = 0 Then Select Case Selection(x).
EntireRow.
Hidden Case True Selection.
EntireRow.
Hidden = False Exit sub Case False Selection(x).
EntireRow.
Hidden = True End Select End If Next While this is a simple code snippet it could be used "as is" in certain situations.
For example if the same search is used repeatedly it might be a good candidate for creating a simple macro button to run the code, rather than implement a complicated solution involving VBA User Forms.
Summary Although this code mimics filtering that is readily available in Excel, most users find that a little VBA knowledge will improve functionality relevant to particular situations rather than relying on a general solution.


Leave a reply