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.
Name, Invoice#
ABC Ltd,123
Johns Company,124
ABC Ltd,234
DEF Ltd,345
ABC Ltd,432
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:
Sheets(M).Activate
searchfor = ActiveCell.Value
If ActiveCell.Column <> B Then
MsgBox "Please choose a cell in column B"
Exit Sub
End If
Next, we choose the column to look and loop by means of the info to seek out the search string:
Range("a1").CurrentRegion.Columns(M).Select
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
Case True
Selection.EntireRow.Hidden = False
Exit sub
Case False
Selection(x).EntireRow.Hidden = True
End Select
End If
Next
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.
Summary
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.
