Sufia Monjil, Ground Floor 1029
Zakir Hussain Road, Chittagong
+8801863612563
+8801863612563
info@gitsolutionsbd.com
support@gitsolutionsbd.com

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.


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.


Leave a Reply

Your email address will not be published. Required fields are marked *