Run Macro On AutoFilter Use
Jul 13, 2007
I am fighting a quixotic battle against a problem, that I realise may well be unsolvable. I have a worksheet that consists of approximately 27,000 rows in a four-level structure, like so:
1
1.1
1.1.1
1.1.1.1
To make the sheet easier to navigate I have created an expanding/collapsing tree structure by using simple hide and unhide rows, and on the last level of headings (1.1.1) I have cells that change between "+" and "-" depending on whether the fourth-level subset is currently visible or hidden. Naturally I need to keep the cell as "+" when the set is collapsed, and "-" when expanded. I have accounted for all methods user can change the hidden settings of those rows, but one:
When users autofilter the list, the +/- signs obviously screw up, because filtering resets the user-set hidden-settings. Suddenly I have expanded subsets with "-" on headings that were previously "+". Now this is obviously a vanity problem, as the +/- sign is not integral to the working of this macro, but the problem is that with three different levels of headers the worksheets starts to look a little cluttered.
Finally, my question: Is there any way to trigger a procedure to reset these signs upon/after autofilter? I realize that this is not a built-in Excel event, but a workaround will do just fine. Any way to detect that an autofiltering has taken place without having to check the.
View 4 Replies
ADVERTISEMENT
Jul 17, 2007
I have an Excel 2003 worksheet that has a list (Data > List > Create List), which displays the AutoFilters for each column in the list. I am seeking a macro that will filter the results (Custom > does not contain "Closed").
I would like to assign the macro to a button as the casual user might not understand the AutoFilter use.
The worksheet in VBE is defined as "Sheet3 (Audit Findings)"
My list has headers on row 7 (A7:K7)
I would like the AutoFilter to return all results except those marked as "Closed" in column K.
View 9 Replies
View Related
Sep 25, 2009
Im sure this is a very common problem. I tried searching for it but I havent found anything that solves this for me. Here is the code Im using:
View 3 Replies
View Related
Aug 7, 2014
My problem is following: I have a list of data that are classified according to a particular character, and I want to copy (with auto filter through the macro) the relevant information to the appropriate place in the sheet where it belongs. That's no problem. The problem is that if I want to copy data, classified by a character that is not listed in the table (that is not in the filtering criteria), then all the data are copied to the appropriate place. But I do not want to copy in this case nothing. How should such a macro look like?
Find attached an example : example.xlsm‎
View 3 Replies
View Related
Jul 19, 2007
I have a main workbook with 40,000 lines of data for various locations. Column A shows the locations. They all have the same fields in column B to N
I am looking for a macro that will filter on column A (Location name) & for every location in that’s in there,
Copy it, open a new worksheet, paste the data for that location into it, plus, name the tab the same as the location name that’s been pasted in there.
I attach a workbook, of desired results.
View 12 Replies
View Related
Sep 15, 2009
I am trying to create a macro to autofilter a sheet based on the value a user will enter into a popup box. I have found bits of code which I have been attempting to figure out and use somehow however I am getting more lost.
I can create a basic macro to autofilter, the problem I am having is that I am unsure of how to link this to an input/popup box of some sort
View 10 Replies
View Related
Oct 22, 2013
a macro (that i will link to a control button) that will autofilter a data set. The problem i have is the macro i wrote below, It might not be the best looking macro in the world, but it would work for my purposes if i can get the part that does the autofiltering to be more dynamic. meaning, instead of a hard coding "Retail" in the macro, id like it to reference a cell so that the user can type whatever they want, then click the button and it will filter based on what they type in.
this is what i have
Sub Filter_Button()
With Sheet2
AutoFilterMode = False
Range("A6:M6").AutoFilter
Range("A6:M6").AutoFilter Field:=2, Criteria1:="Retail"
End With
End Sub
View 5 Replies
View Related
May 23, 2014
I know how to filter based on cell value, and how to auto filter "does not contain", but is it possible to combine these? i.e. Filter OUT the value of a cell from a range?
View 3 Replies
View Related
Feb 23, 2010
I am looking for a vba which will send an email to someone the minute I highlight a row in red?
View 9 Replies
View Related
Jan 9, 2007
I recorded a macro to custom AutoFilter 2 columns in a worksheet. The information will be filtered between 2 dates; i.e. >= cell B2 and <= cell C2.
Both cells are formated to dd/mm/yy so i entered 1/12/06 (for 1st Dec) in cell B2 and 31/12/06 (for 31st Dec) in cell C2.
When i enter these 2 dates, click the button to run the macro and check the custom filter in the "Planning" worksheet it appears that the dates change to 12/01/06 and 21/12/06 and no records appear - although i know at least 65 rows should show.
If i then enter the dates in B2 and C2 as mm/dd/yy instead (but leave the date format of the cell as dd/mm/yy) the filter works perfectly; i.e. enter 12/01/06 and 12/31/06. However I plan to issue this spreadsheet out to other staff and as every other date in the spreadsheet is in the uk format dd/mm/yy i dont want to confuse them by insisting they use the mm/dd/yy format for this one function.
Has anyone ever encountered this before? and if so is there some way i can fix it?
Heres the code for the filter:
Sub Monthly_Stats()
Sheets("Planning").Select
Selection.AutoFilter Field:=5, Criteria1:=">=" & Sheets("Filtered Statistics").Range("B2").Value, Operator:=xlAnd _
, Criteria2:="<=" & Sheets("Filtered Statistics").Range("C2").Value, Operator:=xlAnd
Selection.AutoFilter Field:=82, Criteria1:="<>"
End Sub
View 3 Replies
View Related
Oct 21, 2009
The code contain using Autofilter to some columns, & then copy paste to another sheet to first blank row, & sorting it by asscending in Column A.
Both sheets are in the same workbook.
But for some reason, the Macro will giving the result expected if i run it step by step (by using F8 button under window VB editor). And if i call the Macro name it stoped in the middle of the process.
View 7 Replies
View Related
Jul 7, 2006
I'm using Excel to map automotive parts to vehicle applications (two different spreadsheets) for importation into a relational Access database. Production dates for parts rarely match the on-sale dates of the cars, so I've set up a macro that:
1. copies the start production date for a part record into the vehicle workbook at the top of the 'discontinued' column,
2. copies the end production date for the part into the vehicle workbook at the top of the 'release' column,
3. uses these pasted data points as criteria for the autofilter in the vehicle workbook as 'vehicle release date' < 'part end production date' and 'vehicle discontinued date' > 'part start production date'.
I find that the macro works perfectly in the autofilter for the vehicle discontinued date, but not for filtering the vehicle release date. The date is copied and pasted OK. When I check the custom filter dialogue box, the date has been entered in with the 'is less than' menu item selected, but it doesn't bring up any records unless you click on the 'OK' button once the dialogue box is open. I want to run the macro without having to run the autofilter manually at all. To troubleshoot the issue, I broke up the macro into two separate macros, one for release and the other for discontinued. Problem remains, even though the only difference between the two macros is the relative cell addresses. I've checked formatting of the cells for text vs. numeric and that doesn't appear to be a problem.
View 4 Replies
View Related
Dec 8, 2006
Two macros have been developed in order to write entries to a sheet called Detention Register from another sheet named Database.
The first macro (AutofilterDatabase1) filters for two criteria in the Database sheet: 1. All dates 7 or more days prior to today's date (5th field called rDate) & 2. The smallest positive value for a person in field 10 (called rSmallest).
Once these filters have been applied a second macro (named WriteDetentionRegister) is supposed to write the filtered entries to a sheet called Detention Register. However, if no enties at all are found by the filter it writes all entries to the Detention Register. Is there any way of stopping the writing process if no entries at all are found after the filtering process?
I'd also prefer it if after the writing process (or not as the case may be) that all autofilters were returned to displaying 'All' data in the Database sheet for fields 5 and 10. Is this an easy tweak to make to the existing code?
I enclose the code of the 2 macros.
Sub AutoFilterDataBase1()
Application. ScreenUpdating = False
'Get today and convert to serial value, subtracting 7 days
d = CLng(Date) - 7
DBase.Activate
DBase.AutoFilterMode = False ......................
View 9 Replies
View Related
Feb 4, 2007
I'm having in trying to run a macro. The macro entails a simple copy and paste special over to a new worksheet. I used the auto filter as well to get rid of the non-blanks. It works fine when I copy and paste special over to the new worksheet, but as soon as I run the macro, it doesn't copy over and returns an error.
Range("A1").Select
Sheets("FEF").Select
Rows("1:390").Select
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:="<>"
Selection.AutoFilter Field:=5
Selection.copy
Selection.AutoFilter Field:=5, Criteria1:="<>"
Sheets("FEFFinished").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub
View 2 Replies
View Related
Apr 7, 2007
I have a column in a sheet that has two different values in it "SB" and "SEI".
I want a macro that will allow me to switch the auto-filter between the 2 at with a shortcut button.
I have a macro that will switch the auto-filter on and off, but I was wondering if anyone had anything that will automatically switch it between two values.
View 5 Replies
View Related
Jul 11, 2007
working with a macro to auto filter out all NON-NUMERICS in a column chosen by the user. heres the code i'm working with now:
Sub DeleteAllAlpha()
Dim rTable As Range
Dim lCol As Long
Dim vCriteria
On Error Resume Next
With Selection
If .Cells.Count > 1 Then
Set rTable = Selection
Else
Set rTable = . CurrentRegion
On Error Goto 0
End If
End With
If rTable Is Nothing Or rTable.Cells.Count = 1 Or WorksheetFunction. CountA(rTable) < 2 Then............................
View 4 Replies
View Related
Sep 3, 2007
I would like to include a filter in my macro that shows orders with current date, and sometimes current date minus one (yesterday). This is a monthly recurring task.
In the custom filter I tried: "equal to @Today" but that didn't work. Can someone tell me how I can solve the problem? Here's a snippet from my macro, filterne on something else Selection.AutoFilter Field:=21, Criteria1:=">=89" 'STCD_TO_TODAY
View 3 Replies
View Related
Apr 30, 2014
I am having trouble with the following macro,
[Code] .....
When I press F8 and step through the macro, the StartDate and EndDate both seem to populate correctly.
The macro seems to go through the motions but returns NO results, unlike the recorded macro.
Macro code to "Clear Filter".
View 2 Replies
View Related
Dec 23, 2008
I have tried to record a macro and change it to what I need but so far I have not been able to make it work.
I would like to have a macro that will auto filter on colomn 1 in the following way.........BETWEEN >= Today -10days and Today.
View 2 Replies
View Related
Oct 5, 2006
I was wondering if I could pick up the Field number and the criteria from a cell in excel rather than changing the code in VBA.
Range("A1:K1"). AutoFilter Field:=Range("A1"), Criteria1:=Range("B1")
I was trying this code but I got an error message. Does anyone know if I can pick up these information directly from excel.
View 6 Replies
View Related
Sep 3, 2007
I have a macro that automatically updates the field value if certain criterias are met. Now the user asked that TYPE be the first column in the spreadsheet. How do I change the last line in my macro to reflect that?
The range has now also changed from "A1:AT1000" to "A1:AG1000" ..
View 4 Replies
View Related
Apr 30, 2008
I have to create autofilters dynamically on x columns based on the selection I make. I created this code but is not working.
LastColumn = Cells. Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
Range(Cells(17, 1), Cells(17, LastColumn)).Select
With Selection
.AutoFilter = True
End With
View 2 Replies
View Related
Jun 23, 2009
how to make this recorded macro work with more than the sheet it was recorded on. I need to work with the visible selections. But I'm having all kinds of problems trying to pick just the visible cells. Not having any luck with this project. I tried to included a test book but It keeps giving me an error.
View 4 Replies
View Related
Jun 22, 2007
I need a macro that can change the text in Field 46 from Criteria1 to something else, ie from "To be capped" to "Capitalised". This is my current
Selection. AutoFilter Field:=39, Criteria1:="OPEN" 'STATUS
Selection.AutoFilter Field:=46, Criteria1:="To be capped" 'TYPE
Range("AT1").Select
how to change the text only on the selection found by AutoFilter?
View 4 Replies
View Related
Feb 11, 2008
i wanted to create a macro that will autofilter my starting dates in I to the newest date entered. So ive recorded the macro: ....
View 9 Replies
View Related
Apr 28, 2014
I'm having a hard time making this maro work in Excel 2010.
I need it to filter out the items "AR", "BATCH", and the line of "Total:*" where the * is a total amount of any given number dependant on the day.
Below is the coding I have that Excel is not liking.
Sub FilterAccurateRawData()
'
' FilterAccurateRawData Macro
'
'
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AA$45415").AutoFilter Field:=1, Criteria1:=Array("<>AR", "<>BATCH", "<>Total:*")
Operator:=xlFilterValues
Sheets("Instructions").Select
Range("A9").Select
End Sub
View 3 Replies
View Related
Aug 20, 2014
I can select the top cell in column "F" after filtering by multiple columns using VBA and arrays, but now want to I want to use the top cell in column "F" to search for all other equipment that uses this item.
E.g. remove filter, and reapply autofilter to column "F" based on selected cell as per below VBA
Note: Row 1 contains command buttons and row 2 Headers.
View 4 Replies
View Related
Jan 16, 2007
I can set the autofilter, change it, remove all using Macros. What I need to know how to do is reset a filtered column to ALL while leaving all other filters on.
View 2 Replies
View Related
Oct 22, 2008
I am trying to make an "intelligent" auto filter that with filter with increasing restriction until a certain criteria is met.
The list runs from A5:G20. In coloumn G is the number of hours associated with each event. And in A1 I have the percentage of items showing/whole list so it I have 15 rows on the list, and I filter so that only 5 are showing, cell a1=33%
How can I make a macro that will autofilter until the the a1=5%
Like having filter criter = equal or greater then 1 hour,
if a1 > 5%
Then criteria + 1 hour
If A1=<5%, then stop.
basically a seed criteria of 1 hour, adding 1 hour until the value in a1 = 5%
View 6 Replies
View Related
Jul 25, 2009
auto filter options in excel. For some reason it is only sorting up to row 140.
View 14 Replies
View Related
