Hello there, hope you all are well, as the topic is to create advance filter in Microsoft Excel using macro tool, we will discuss in detail about it today, and after taking this note you will not have any doubt about data search process using advance filter. Let’s begin with the tutorial…
Software Required: here I’m using Microsoft Office 2019, you can use upper version as well.
Knowledge: You need only basic Excel knowledge to do the same.
Step by Step Process:
- First Create a database in Sheet1 as below
- Insert a new sheet, To take a new sheet Look at below at Excel Navigator > Click on “+” Icon to create a new sheet with the name “Sheet2”
- Go to Sheet2 and Make this Search Field as below
- Now Click on View tab> Macro > click on Record Macro button
- Record Macro dialog box has appeared on the screen as above image. Type the Macro Name as you like and in Shortcut key box press a letter “k” after Ctrl > click on OK
- Your macro recording has been started now, so do the next steps
- Click on Data Tab- Click on Advanced button, this below dialog box will appear
- Click on List Range box > Go to Sheet1 Select $A$1:$C$6
- Click on Criteria Range box> Select ID_NO search field two Vertical Cells
- Now Click on Copy to another location option button, you can see Copy to: text box is now active> here you have to select the horizontal cells from ID_NO to Salary (Don’t select 2 Rows, Only select Black colored Heading cells area)
- Click on OK
- go to Macro> Stop Macro
- Now Test your Job
- Type any ID_No as in your database- press ENTER- Press Ctrl + K (Which shortcut key you have given during macro record window)
- Hope it’s working!
Thank you, If you have any query the please Ask here