Filter Tricks with MS Excel
Recorded on August 12th, 2016
Earlier today, I was looking for a way in MS Excel to make a button that would filter on only the rows that contained a certain pre-defined string. I googled and saw an answer on Stack Overflow that said to just record a macro. (Duh! I should have thought of that.) I recorded a macro of me doing the filtering manually and assigned a keyboard shortcut of Ctrl+M to it, which I don’t believe is used already, and is better than creating a button.
Of course, after that I needed a keyboard shortcut to clear a filter just as easily, but still leave auto-filtering on. That shortcut is built into Excel: Just hit ALT+A+C. (Found the answer on ExcelCampus).
Lastly, I also needed a way to filter for rows that matched whatever the current cell value was, in the column I was in. I tried making a macro, but it ended up filtering on whatever the cell value was at the time I recorded the macro, because that value was burned into the VB code. I needed a way to filter on the value of the actual current cell, no matter what cell that was. I found the answer on MSDN, and it turns out that if you record the macro and then change the VB code to reference Column(ActiveCell), instead of “blahvalue”, that works. I assigned Ctrl+Q to that. Nice!