![]() It is the first time using a new FAS account "jhford", which is what I'd rather use from now on. This is not my first package, but the first in just under 10 years. Set rngAutofilter = Cells(, ).Description: wxHe圎ditor is a graphical hex editor that I likeįedora Account System Username: jhford (also have john64, but not using it any more) If myAutofilters(i, 3) 0 Then 'then is either xlAnd, xlOr, etc., and there's a second criteria SaveAutoFilterInfo = SaveAutoFilterInfo + 1 ReDim myAutofilters(1 To iNumAutofilters, 4) If iNumAutofilters Then MsgBox "I can't explain this. SaveAutoFilterInfo = 0 'counts the number that are. 'tofiltermode=false 'just FYI, how you comprehensively turn off filtering on a sheet (erasing the dropdowns and criteria and filter range!)įunction SaveAutoFilterInfo(iNumAutofilters As Long) As Long Rem Selection.AutoFilter Field:=i 'How you'd "turn off" only a single column's autofiltering. RngAutofilter.AutoFilter Field:=i, Criteria1:=myAutofilters(i, 2) ', On:=true by rule (it's R/O anyway) RngAutofilter.AutoFilter Field:=i, Criteria1:=myAutofilters(i, 2), Operator:=myAutofilters(i, 3), Criteria2:=myAutofilters(i, 4) ', On:=true by rule Operator is something, so set it and Criteria2, else just Criteria1 INumActiveAutofilters = SaveAutoFilterInfo(iNumAutofilters) 'NOTE! Use CALL or assignment to prevent parentheses from forcing ByVal ! autofilter.applyfilter, but not the invaluable XL03 and earlierĭim i As Long, iNumAutofilters As Long, iNumActiveAutofilters As Long 'This will update the autofilter display to recognize data changes by turning autofilter off and then on, preserving all characteristics Public myAutofilters As Variant, rngAutofilter As Range 'Public Now the autofilter will be reinstituted, over the same range as it was before your code began, but with the autofilter updated for changes in data. RngAutofilter.AutoFilter Field:=i, Criteria1:=myAutofilters(i,2), Operator:=myAutofilters(i,3), Criteria2:=myAutofilters(i,4) RngAutofilter.AutoFilter Field:=i, Criteria1:=myAutofilters(i,2) Again take care that you avoid "Application-defined errors" when. On according to your saved array, reset 3 of the 4 properties of each of the. ShowAllDataįor each filter Item that was. Turn off the filter but preserve the dropdowns with. (myAutofilters would be reDim'd to the number of rows and columns in step 1) autofilter Items, taking care that you avoid "Application-defined errors" when. columns, and (./.) rows, saved to rngAutofilterĬollect in an array myAutofilters each of the 4 properties of each of the. Assume With ActiveSheet for the following (or some other sheet object): Actually, you can do this more generally. Perhaps the inexplicable downvote count on this answer can be reversed when people see what good stuff is done below.ĭanicotra used a simplified example. I beg that true experts and gurus read the code because I'm pretty confident that it is top shelf material. AutoFilter.ApplyFilter is invalid in XL03 and earlier so I show the way below. Is the correct solution when using Excel 2007+. (Admins, feel free to cut this into a comment above.) User "danicotra" response beginning with "I use a VBA/Macro based on Worksheet_Change event too, but my approach." with ![]() For more reference on Range.AutoFilter method, see: In my example, I want to show only rows where L column differs from 0 (hence I used "0"). The Criteria is a string that describes the filter to apply to data range. In my case, I only have a single column filter and the range is made by a single column (L) which is the first in the range (therefore I use 1 as value). What need a bit of explanation here are Field and Criteria. The operation is simple: as something gets changed on my sheet, the filter on the specified range gets removed/reapplied again so to have it refreshed. In my example I'm assuming to have a simple filter on a single column (L in my case) and that my data range is on rows from 1 (even if it may contain heading) to 126 (choose a number great enough to be sure). (Use Alt+ F11 keys combination to make development panel appear and paste the code to the worksheet containing the filter you want to get auto-refreshed.) ![]() Ok, first the code and then the explanations: Private Sub Worksheet_Change(ByVal Target As Range)ĪctiveSheet.Range("$L$1:$L$126").AutoFilter Field:=1ĪctiveSheet.Range("$L$1:$L$126").AutoFilter Field:=1, Criteria1:="0" I use a VBA/Macro based on Worksheet_Change event too, but my approach is slightly different.
0 Comments
Leave a Reply. |