|
Remove All Filtering From a Worksheet in Excel
This Excel macro removes all filtering from a worksheet in Excel. This allows you to completely remove any filter now matter how complex from any worksheet in Excel. This means that the entire original data set will then be visible in Excel.
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 Remove All Filtering From a Worksheet in Excel
Sub AutoFilter_Remove()
'This macro removes any filtering in order to display all of the data but it does not remove the filter arrows
ActiveSheet.ShowAllData
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
I have a workbook that I received from a vendor that when I click in a cell a comment appears. There is no line or comment indicator for each comment. So I went into the tools - options - views - comments section and chose no comments. The comments still appear and when I go to the tool bar - view - comments nothing appears. I checked for any VBA code and didn't see anything. I was able to get around this problem by copy/paste special into a new worksheet, but curiosity is killing this cat. Any ideas?
We had an Excel worksheet that is used everyday suddenly pop up with
protection. I have used protection before, but this one never had a
password. We put in every known password we use, but none worked. I
resigned to re-typing the worksheet, then, inbetween Friday night, and
Saturday noon, when most of the office is out, a different Excel worksheet
has the same problem. We are using Excel 97. Any help would be appreciated.
I filter my spreadsheet then read the save file into Visio. Visio reads the file as if there was no filtering. I haved saved it as a xls & txt files with the same results. Is there something I can do to this file before I save it so Visio will no read the filtered rows? If yes how can this be done? Thanks!
Hello there. I have two worksheets containing customer details-worksheet 1 contains current customers-column A containing unique numeric customer identifiers. Worksheet 2 contains a larger customer list, some of which are obsolete, but column A also contains the customer identifiers. I want to filter worksheet 2 so that customers that don't appear in worksheet 1 are removed. Does anyone have any ideas? Many thanks,
Mattbro
Hello there. I have two worksheets containing customer details-worksheet 1 contains current customers-column A containing unique numeric customer identifiers. Worksheet 2 contains a larger customer list, some of which are obsolete, but column A also contains the customer identifiers. I want to filter worksheet 2 so that customers that don't appear in worksheet 1 are removed. Does anyone have any ideas? Many thanks,
Mattbro
Hello,
Thought I'd turn here as my brain is fried.
I'm not very skilled when it comes to Excel.
I have a large list of postcodes for which I require addresses. I have a HUGE list of addresses, covering the postcodes I require and many many more.
How do I rid myself of the many many more and filter the list of addresses to only cover the postcodes I want?
Essentially I have a list of postcodes in worksheet A, and a list of addresses, including postcodes, in worksheet B. I want to filter worksheet B so it only covers the postcodes in worksheet A.
Apologies if this is unclear!
I have a feeling there's a simple solution out there, but I surely can't find it!
Thanks
HI,
I was wondering if anyone might now if you can filter by worksheet. I have a workbook that has 13 worksheets with tables in it - 1 for each month of the year and 1 for a combined total of the other 12 worksheets.
On the worksheet for the combined total, is there a way to filter by worksheet the worsksheet(s) that you only want to see at a given time? Example: the worksheet for the combined total has data totaled for 9 months. I would like to know if I could filter out 1 or 2 months to only see data for 7 or 8 months off the combined worksheet.
Please let me know if it is possible.
Thank you
Currently, I have a large listing of email in an excel that I use to send out emails. This is like my master file pretty much and contains like 50,000 entries. But recently I was given an excel worksheet of users who wish to opt-out of receiving emails, about 9,000 entries. So I must find a way to remove those 9000 entries found in the one worksheet from that 50,000 entires found in the other worksheet, with out having to manually search for each entry. I am pretty much at a loss and if anyone could give me some help or advice I would appreciate it. I was told to use macros but I am not very good at them.
Hi.
I have Excel 2003.
I've got 2 worksheets: 1 with column A full of account numbers and column B with data in some of the rows.
I've got a second worksheet with some of the same account numbers and no data in column B.
I need to copy the notes in column B from worksheet 1 to column B of the relevant account numbers in worksheet 2. Is there a program that will do this?
Thanks
Steve (Cheltenham UK)
Hi,
I need to do sorting and filtering the similar records to new worksheet. I need you help to do this job, give some idea or command to use for the following steps.
I have attached the worksheet newtran.xls.
1. I need to find the last column, the column will be increased or decreases. so that we need to find the last column at time of running the macro. Now the value is there till column DD
2. I need to concatenate the column B, D, F to last column find out at 1 step. Insert a new column and place the concatenated value.
3. Entire row will be Sorted based on concatenated value column.
4. Find the similar row on concatenated column and entire row need to be moved to new worksheet.
Advance Thanks For your kind help.
Regards,
Sai Kumar.
|
|
