Shawn sends in a great tip for today's podcast. Access has a Filter by Selection icon. Using a few lines of VBA code, you can add this functionality to your personal macro workbook. Episode 839 shows you how.
Here is the code:
Sub Filter_by_Active_Cell()
Dim ColNum As Integer
ColNum = ActiveCell.Column - _
(ActiveCell.CurrentRegion.Column - 1)
Selection.AutoFilter Field:=ColNum, Criteria1:=ActiveCell
End Sub
Sub AutoFilterToggle()
Selection.AutoFilter
End Sub
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Here is the code:
Sub Filter_by_Active_Cell()
Dim ColNum As Integer
ColNum = ActiveCell.Column - _
(ActiveCell.CurrentRegion.Column - 1)
Selection.AutoFilter Field:=ColNum, Criteria1:=ActiveCell
End Sub
Sub AutoFilterToggle()
Selection.AutoFilter
End Sub
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Great tip today, sent in by Sean.
Sean had sent a little bit of VBA code-- let me set this up though.
You know we can turn on the Auto Filter or, in Excel 2007, the Filter, and quickly filter data sets.
So we turn on the Auto Filter and then we basically have drop-downs here where we can go through and choose one particular customer.
Well, there's a great feature to access called Filter by Selection.
So let me turn this off, Data, Filter, Turn off the Auto Filter.
And Sean sent some code-- let's take a look at the code-- already had to adjust my personal macro work book-- called Filter By Active Cell, and basically, it's two lines of code.
The first line of code says, "Hey, what column number are we in?" So, you know, if I'm in the Customer column-- let's take a look at the worksheet here, the Customer column, well that's the sixth column in the spreadsheet or the fourth column in the data set.
And once Sean knows that, then he turns on the Auto Filter, filters the current column to the value in the active cell.
So this is pretty cool.
Let's just come here and choose State Farm and I'll press Alt+F8 to run that.
It turns on the filter and we get just the State Farm records.
And then [ Inaudible at 00:01:29 ] We're going to go into Alt+F8; and we're going to choose Filter by Active Cell; and then Options.
Now, Sean used Q and W just because they were easy and right up there in the top left-hand corner.
AutoFilterToggle, we'll make that one be W, click OK, click Cancel.
So now Ctrl+W brings everything back; Ctrl+Q gives us just the records in the selection.
What an amazing little feature.
Now, what i would rather do, is have an icon up in the toolbar.
So in Excel 2003 we'll go to Tools, Customize, and on the left-hand drop-down we'll find Macros, add a new Custom Menu Item up there, right click, Assign Macro, and that will be Filtered by Active cell.
Okay, now, Custom Menu Item, that's a little frustrating to me.
It'll be nice if we had a nice little button image there.
So this is an amazing trick: I'm going to switch over to Access, where we do have this icon-- we have this icon-- and I'm going to go into Customize here in Access, and basically right-click on that icon and say Copy Button Image; now I'll switch back to Excel; right-click here and say Paste Button Image; and now we can say, basically, that we want this to be, not Image and Text, but the default style which gives us just a single icon.
So check this out: I can choose any Cell, maybe like D, E, F, hit the icon, and I've instantly filtered the data set to just that item.
What a cool, little macro.
I mean, it's really just a couple lines of code with a dim statement.
So, I want to thank Sean for sending it in, I'll be sending an Excel Master pin his way for this great idea.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
Great tip today, sent in by Sean.
Sean had sent a little bit of VBA code-- let me set this up though.
You know we can turn on the Auto Filter or, in Excel 2007, the Filter, and quickly filter data sets.
So we turn on the Auto Filter and then we basically have drop-downs here where we can go through and choose one particular customer.
Well, there's a great feature to access called Filter by Selection.
So let me turn this off, Data, Filter, Turn off the Auto Filter.
And Sean sent some code-- let's take a look at the code-- already had to adjust my personal macro work book-- called Filter By Active Cell, and basically, it's two lines of code.
The first line of code says, "Hey, what column number are we in?" So, you know, if I'm in the Customer column-- let's take a look at the worksheet here, the Customer column, well that's the sixth column in the spreadsheet or the fourth column in the data set.
And once Sean knows that, then he turns on the Auto Filter, filters the current column to the value in the active cell.
So this is pretty cool.
Let's just come here and choose State Farm and I'll press Alt+F8 to run that.
It turns on the filter and we get just the State Farm records.
And then [ Inaudible at 00:01:29 ] We're going to go into Alt+F8; and we're going to choose Filter by Active Cell; and then Options.
Now, Sean used Q and W just because they were easy and right up there in the top left-hand corner.
AutoFilterToggle, we'll make that one be W, click OK, click Cancel.
So now Ctrl+W brings everything back; Ctrl+Q gives us just the records in the selection.
What an amazing little feature.
Now, what i would rather do, is have an icon up in the toolbar.
So in Excel 2003 we'll go to Tools, Customize, and on the left-hand drop-down we'll find Macros, add a new Custom Menu Item up there, right click, Assign Macro, and that will be Filtered by Active cell.
Okay, now, Custom Menu Item, that's a little frustrating to me.
It'll be nice if we had a nice little button image there.
So this is an amazing trick: I'm going to switch over to Access, where we do have this icon-- we have this icon-- and I'm going to go into Customize here in Access, and basically right-click on that icon and say Copy Button Image; now I'll switch back to Excel; right-click here and say Paste Button Image; and now we can say, basically, that we want this to be, not Image and Text, but the default style which gives us just a single icon.
So check this out: I can choose any Cell, maybe like D, E, F, hit the icon, and I've instantly filtered the data set to just that item.
What a cool, little macro.
I mean, it's really just a couple lines of code with a dim statement.
So, I want to thank Sean for sending it in, I'll be sending an Excel Master pin his way for this great idea.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
