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.

Andy L Gibson is a former Web Site programmer rediscovering his interest in Excel applications for small business. His blog at http://solutions4business.wordpress.com/ holds the VBA code for all of his articles on Excel and is available for FREE download. He is developing an Excel application which will contain working examples of VBA methods and solutions.

View the original article here