Скачать или смотреть Excel - Filter Data Sets with a Single Click - Easy VBA Code and Custom Toolbar - Episode 839

Microsoft Excel Tutorial: Filter Data Sets with a Single Click - Easy VBA Code and Custom Toolbar.

Welcome back to the MrExcel netcast, where we share helpful tips and tricks for using Excel. In today's video, we'll be discussing a great feature called "Filter by Selection" that was sent in by one of our viewers, Sean. This feature allows you to quickly filter data sets by selecting a specific value, making data analysis much more efficient.

To use this feature, first turn off the Auto Filter or Filter in Excel 2007. Then, use the VBA code provided by Sean, which can be found in the description below, to filter the current column to the value in the active cell. For example, if you select "State Farm" in the Customer column and run the code, the data set will be filtered to only show records for State Farm.

But why stop there? We can take this feature a step further by creating a custom menu item or button in the toolbar for even quicker access. In Excel 2003, go to Tools, Customize, and add a new custom menu item. Then, assign the macro "Filtered by Active Cell" to this menu item. You can also add an icon to the button by copying and pasting one from another program, such as Access.

With this custom menu item or button, you can easily filter data sets by simply selecting a cell and clicking the icon. This is a great time-saving trick that only requires a few lines of code. Thank you to Sean for sharing this idea with us, and as a token of our appreciation, we'll be sending him an Excel Master pin. Thank you for watching, and be sure to tune in for more helpful tips and tricks from MrExcel.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...


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

Table of Contents:
(00:00) Introduction to the tip sent in by Sean
(00:22) Explanation of the Auto Filter feature in Excel
(00:45) Introduction to the VBA code sent by Sean
(00:55) Explanation of the code and how it works
(01:15) Demonstration of the code in action
(01:52) Customizing the code for easier use
(02:19) Adding an icon to the toolbar for quick access
(03:05) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Adding a button image to a custom menu item in Excel
Auto Filter and Filter in Excel 2007
Copying button image from Access to Excel
Customizing Excel toolbar with macros
Excel Master pin for creative VBA code
Filter by Selection feature in Excel
Filtering data set with a single icon in Excel
MrExcel netcast on Excel tips and tricks
Simple VBA macro to filter data by active cell
Using Auto Filter to filter data by active cell value
VBA code to filter data by active cell
YouTube tutorial on Excel VBA code

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...