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

How To Hide And Unhide Rows With Your Own VBA Filter In Excel
By Andy L Gibson

While the tools in Excel in support of filtering data are proficient, they can be a little tricky to use up and it might be sensible exploring a VBA solution in support of extracting the in turn you need from your database.

Wearing this article, we'll create a code morsel to facilitate hides data and toggles the filter on and rotten. While paying attention on a uncomplicated scenario the code can be enhanced to create your own filtering tool.

Creating A Simple Toggle Filter With VBA

The scenario we'll look on is simply to extract all the rows in a index to facilitate contain a guaranteed customer renown.


Name, Invoice#

Nuts and bolts Ltd,123

Johns Company,124

Nuts and bolts Ltd,234

DEF Ltd,345

Nuts and bolts Ltd,432

We'd only like the user to first-rate a customer renown in the index and the code must secrete other rows not containing the renown. If the code is run again the filter must be impassive.

When the user clicks on a renown to search in support of the code ensures the selected cell is in editorial 1:


Sheets(1).Activate

Searchfor = ActiveCell.Value

If ActiveCell.Column <> 1 Then

MsgBox "Please first-rate a cell in editorial 1"

Exit Sub

End If

Next, we first-rate the editorial to search and ring through the data to discover the search row:


Range("a1").CurrentRegion.Columns(1).Select

For x = 2 To Selection.Count

Now we obtain to determine whether or not the filter is in place.

If the cell does not match the search text AND the row is hidden, it follows that the filter is in place. Therefore, we earn visible all the rows and exit the routine.

If the cell does is not a match AND the row is visible, it follows that we secrete the row and carry on filtering the data.


If InStr(Selection(x), searchfor) = 0 Then

Select Case Selection(x).EntireRow.Hidden

Case True

Selection.EntireRow.Hidden = False

Exit associate

Case False

Selection(x).EntireRow.Hidden = True

End Select

End If

Next

While this is a uncomplicated code morsel it can be used "as is" in guaranteed situations. For pattern if the same search is used repeatedly it might be a proficient candidate in support of creating a uncomplicated macro button to run the code, quite than apply a complicated solution linking VBA User Forms.

Summary

Although this code mimics filtering to facilitate is readily free in Excel, largely users discover to facilitate a little VBA facts will pick up functionality applicable to specific situations quite than relying on a common solution.