|
Filter Data in Excel Without the Filter "Arrow" Appearing in the Filtered Column - AutoFilter
This Excel macro filters data in Excel without the filter "arrow" appearing in the column that is being filtered. This macro can be used to make a filtered data set appear more aesthetically pleasing ot to simply to try mask the fact that data has been filtered or make it harder for the user to re-filter the data in Excel.
However you want to use this macro, know that it might take some tinkering to get it to work just like you want it to work. This is because of the fact that by default the only column that wont have the filter arrow is the column that is being filtered. Also, to use the macro simply update the field number and replace "Enter Criteria Here" with your own criteria.
How Filter Macros Work
All of the elements below often appear within autofilter macros.
Range
This should be the start of the data set, table, or list which you would like to filter. This can also be the entire range reference to the table. For example, if the table was from cell A1:D450, you could put that as the range or you could put A1 as the range. You can do this because the autofilter feature in Excel will automatically try to determine the total range which you would like to filter.
Field
This is the number of the column within the data table that you would like to filter. The first field (1) is the very first column in the data set that will be filtered. This means that if your data starts in column B and your range is Range("B1") and you want to filter based on column D, you would put 3 in for the field.
Criteria
This is the criteria by which you would like to filter. Some of the macros have symbols within the quotation marks after this argument and those symbols (such as <,>,*,?, etc.) should be left where they are in order to retain the functionality of the macro.
Operator
You will not have to change this. This is simply the way to perform different types of filter features in Excel.
Where to install the macro: Module
Excel Macro to Filter Data in Excel Without the Filter "Arrow" Appearing in the Filtered Column - AutoFilter
Sub AutoFilter_in_Excel_No_Arrow()
'This option is a bit finicky depending on how many of the fields you want the drop-down arrow to not be displayed over.
'By default, the visibledropdown option being set to false will only remove the drop-down arrow from the frield(s) being filtered.
Range("A1").AutoFilter Field:=1, Criteria1:="Enter Criteria Here", VisibleDropDown:=False
End Sub
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Similar Helpful Excel Resources
Dear Masters,
I have below data in my sheet with Column heading as (Grade,Number,Copy Number).
Grade Number Copy Number
A 25
B 26
B 30
A 25
A 23
B 25
B 26
A 23
A 34
C 25
C 25
A 25
I applied Autofilter gith Grade-A as selection Criteria. Now I want to copy & paste value of Number Column (relevant to A) to Copy Number Coulmn with Filter Applied. When I do using Copy & Paste it also copied value of B&C grade. Please provide the solution.
Regards,
Vishnu
I can't see it well enough if its navy blue on black like mine is!
----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/office/comm...lic.excel.misc
Hi
I have a coworker whose excel seems to be bugging out. When she sets a column to filter with the autofilter, the column arrow stays black instead of turning blue like it is supposed to, but the arrow on a column 7 to the left of the filtered column does turn blue. This column has not been filtered. Has anyone heard of this problem? What could be causing it? She only has SP1 installed, so I thought maybe that is the problem. She is currently in the process of updating so we will see if that corrects the problem.
Thanks
Hi guys,
I have a shared workbook (*shudder*) with auto filters on each column. When I filter say column D, the 'blue arrow' is on column C even though D is filtered.
This has only just started to happen. If I remove the filters and readd them it works fine, until I reopen the file.
Anyone know what could be causing this, and does anyone have a solution?
Cheers,
James
I would like to know if it's possible to change the colour of the filter
arrow when a spreadsheet is set on auto-filter as I find the blue colour is
not too easy to see - I would prefer to set it to red. Does anyone know if
this is possible?
Thanks.
Nice to see u again,
In an excel sheet, I tried to filter the Column 'Group', this group column containd around 10 group names.
I am using vba to filter the column, my qn, is there any way to read each group name under the filtered column 'Group' usin vba?
Thanks in advance for any help
Hi,
I needed a macro to filter the column # 5 (SL Rollup) for :
1. "Tax Acctg&Risk Advisory Svcs" and rename it as "TARAS"
2. "Personal Tax Services Total" and rename it as "PTS"
3. "Business Tax Services (BTS) - 0214" and rename it as "BTS - 0214"
Actually when i record the macro its not taking up.
Note : Here in the worksheet the data comes from an access database.
Thnks in advance.
Mehul
Hello, all!
I have a very wide spreadsheet (to column BD) of dynamic length. I was wondering if it was possible to:
Filter the list by the value in Column A (there's about 8-10 different possible values for Column A throughout the 3000+ rows), and save each filtered list as its own CSV file named after the value in Column A.
For example, let's say 400 of the rows has "Steve" as the value in Column A. Ideally, the macro would filter all the "Steve" rows and save it as a CSV file named "Steve". Then, it would filter out the next name, and save the next one, etc...
Is this possible?
Have spent a good while researching this and haven't found anything.
All I "simply" want to do is use auto filter and advanced filter to filter a column of numbers based on the first digits.
For example, let's say I have a column of data such as:-
177990
177346
189344
905343
177343
905454
I want to enter criteria such that I select all the rows that begin with "177".
You might have thought this was easy and just need to use 177* with the "begin with" auto filter statement or "=177*" with advanced filter. However, both criteria select nothing.
After a little testing I realised this is because excel must store the numbers as numbers (i.e. not strings). So if I insert a ' in all columns the above criteria works but NOT if I use the criteria as above.
The *silly* thing is if I try to match the whole number (not just first 3 digits") the criteria works fine.
Now, surely I'm missing something simple here and there is a way to filter part of a number?
Thanks!
I have a spreadsheet that I want to sum the data similar to SUMIF or SUBTOTAL after a filter is applied. So I have data in column "F" that I appliy a filter and based on that filter I want to sum only the filtered totals in column H. Is there an easy way or is creating a new function the only way to go?
Thanks
|
|
