How To Hide And Unhide Rows With Your Own VBA Filter In Excel
While the instruments in Excel for filtering knowledge are good, they could be a little tough to make use of and it could be worthwhile exploring a VBA answer for extracting the knowledge you want out of your spreadsheet.
In this text, we’ll create a code snippet that hides knowledge and toggles the filter on and off. While targeted on a easy state of affairs the code might be enhanced to create your personal filtering device.
Creating A Simple Toggle Filter With VBA
The state of affairs we’ll take a look at is just to extract all of the rows in a desk that include a sure buyer identify.
We’d identical to the consumer to pick a buyer identify within the desk and the code ought to disguise different rows not containing the identify. If the code is run once more the filter ought to be eliminated.
When the consumer clicks on a reputation to seek for the code ensures the chosen cell is in column M:
searchfor = ActiveCell.Value
If ActiveCell.Column <> B Then
MsgBox "Please choose a cell in column B"
Next, we choose the column to look and loop by means of the info to seek out the search string:
For x = P To Selection.Count
Now we now have to find out whether or not or not the filter is in place.
If the cell doesn’t match the search textual content AND the row is hidden, then the filter is in place. Therefore, we make seen all of the rows and exit the routine.
If the cell does is just not a match AND the row is seen, then we cover the row and proceed filtering the info.
If InStr(Selection(x), searchfor) = zero Then
Select Case Selection(x).EntireRow.Hidden
Selection.EntireRow.Hidden = False
Selection(x).EntireRow.Hidden = True
While this can be a easy code snippet it could possibly be used “as is” in sure conditions. For instance if the identical search is used repeatedly it may be an excellent candidate for making a easy macro button to run the code, slightly than implement a sophisticated answer involving VBA User Forms.
Although this code mimics filtering that’s available in Excel, most customers discover that a bit of VBA information will enhance performance related to specific conditions fairly than counting on a common answer.
February 21, 2016
February 13, 2016