tag:blogger.com,1999:blog-28492262.post9126485637461760261..comments2023-12-27T22:22:25.151-08:00Comments on VBA Tips & Tricks: AutoFilter using Excel VBAUnknownnoreply@blogger.comBlogger11125tag:blogger.com,1999:blog-28492262.post-30580708071441085142016-06-27T15:56:06.310-07:002016-06-27T15:56:06.310-07:00Collin,
Did you receive a response to this questi...Collin, <br />Did you receive a response to this question? I have the same question, but I have not found any answers.<br />JDJDhttps://www.blogger.com/profile/16616974988430733007noreply@blogger.comtag:blogger.com,1999:blog-28492262.post-11834999077333859192014-06-11T09:54:16.999-07:002014-06-11T09:54:16.999-07:00Creating an autofilter array from a range. Exampl...Creating an autofilter array from a range. Example is a range from column C -> 4 rows including the current row. Goes to another sheet and uses the values from the range as the criteria.<br /><br />Dim myarray(10)<br /> i = ActiveCell.Row<br /> For j = 0 To 3<br /> myarray(j) = Range("C" & i + j).Value<br /> Next j<br /> Sheets("data").Select<br /> ActiveSheet.Range("$A$3:$AI$3567").AutoFilter Field:=9, Criteria1:=myarray, Operator:=xlFilterValues<br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-28492262.post-50719458729676211872013-04-17T13:05:39.394-07:002013-04-17T13:05:39.394-07:00Simple answer is to pass an array and add an Opera...Simple answer is to pass an array and add an Operator to the filter:<br />[CODE]<br />.AutoFilter Field:=wkColumn, Criteria1:=Array( _<br /> param2, _<br /> param3, _<br /> param4), _<br /> Operator:=xlFilterValues<br />[/CODE]Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-28492262.post-44958389158495519662013-02-11T14:21:03.384-08:002013-02-11T14:21:03.384-08:00Alexander asked 'What I want is to replace the...Alexander asked 'What I want is to replace the Criteria1:= array with my own array"<br /><br />here's my suggestion...and I am just now starting to work this into one of the reports I support:<br />- Set up the worksheet with the filter as you believe it will be set.<br />- Create the following in VBE behind that worksheet<br />sub stuff()<br />Dim w as worksheet<br />Set w = Thisworkbook.worksheets(1)'or whatever the sheet index is<br />End Sub<br />- Make the execution stop at "End Sub"<br />- Create a watch for "w"<br />Now for the fun...Drill down "w" to "Autofilter" to "Filters"<br />Once at Filters, open each of the Items (each Item is a filter/column)...and look at the entries in Criteria1 and Criteria2. Each will have either a formula for the filter, a code, or break down into an array of formulas.<br />- Make your code produce a String like what you see in Criteria1/2/()...Donhttp://www.blogger.com/blogger.g?blogID=3173231704050434493#overview/postNum=0noreply@blogger.comtag:blogger.com,1999:blog-28492262.post-8954513753546499402010-12-28T21:07:59.389-08:002010-12-28T21:07:59.389-08:00How do you keep the summation of Total Price after...How do you keep the summation of Total Price after filtering?Collinhttps://www.blogger.com/profile/17281787249074233211noreply@blogger.comtag:blogger.com,1999:blog-28492262.post-38603695710261892682010-06-21T19:04:34.429-07:002010-06-21T19:04:34.429-07:00Not sure if you can add a custom function in AutFi...Not sure if you can add a custom function in AutFilter, but you can add the function to a Menu that will sort. Or you can call a subroutine when the text is filteredMathewnoreply@blogger.comtag:blogger.com,1999:blog-28492262.post-31006874641401048872010-06-21T13:49:13.027-07:002010-06-21T13:49:13.027-07:00Excel 2007
My CustomerName has business rules for ...Excel 2007<br />My CustomerName has business rules for the sort order.<br />For Access queries, forms, listboxes, I created a nice custom vba Function that works great.<br /><br />In Excel 2007, I use copyfromrecordset. So the customer name custom order is right the first time.<br /><br />Once users sort by column 2, 3 then want to re-sort CustomerName again.<br /><br />CustomerName sorted by A-Z is not correct.<br /><br />Is there a way to add my custom function to Excel (in the modules) and have it show up in the AutoFilter list choice?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-28492262.post-55013540669630692002009-10-26T06:41:13.593-07:002009-10-26T06:41:13.593-07:00Shasur,
How do I use an array of criteria using a...Shasur,<br /><br />How do I use an array of criteria using autofilter.<br /><br />recording a macro tells me the following :<br />ActiveSheet.UsedRange.AutoFilter Field:=1, Criteria1:=Array("Criteria1", "Criteria2", "Criteria3"), Operator:=xlFilterValues<br /><br />What I want is to replace the Criteria1:= array with my own array<br />so What I have made is this but only the last entry will be visible (obviously) how can I feed the array into the autofilter ? <br /><br /><br />For Each ws In ActiveWorkbook.Sheets<br /><br /> If IsNumeric(ws.Name) Then<br /> <br /> ws.Activate<br /> Set CurrentFilterRange = ws.UsedRange<br /> 'ws.AutoFilterMode = False<br /> <br /> For i = 0 To UBound(FilterList())<br /> CurrentFilterRange.AutoFilter Field:=1, Criteria1:=FilterList(i)<br /> Next i<br /><br /> End If<br /><br />Next ws<br /><br /><br />many thanks <br />AlexanderUnknownhttps://www.blogger.com/profile/03080456590718168183noreply@blogger.comtag:blogger.com,1999:blog-28492262.post-49283927563886850172009-10-26T06:36:28.459-07:002009-10-26T06:36:28.459-07:00Shasur,
How do I feed autofilter an array of crit...Shasur,<br /><br />How do I feed autofilter an array of criteria. in your example you only use 2 criteria and I need to use more mucht more. <br /><br />what I have now is ..<br /><br />For Each ws In ActiveWorkbook.Sheets<br /><br /> If IsNumeric(ws.Name) Then<br /> <br /> ws.Activate<br /> Set CurrentFilterRange = ws.UsedRange<br /> 'ws.AutoFilterMode = False<br /> <br /> For i = 0 To UBound(FilterList())<br /> CurrentFilterRange.AutoFilter Field:=1, Criteria1:=FilterList(i)<br /> Next i<br /><br /> End If<br /><br />Next wsUnknownhttps://www.blogger.com/profile/03080456590718168183noreply@blogger.comtag:blogger.com,1999:blog-28492262.post-63850327681057700562009-05-23T19:19:31.737-07:002009-05-23T19:19:31.737-07:00Can you try
dateadd("d", 7, date)Can you try<br /><br />dateadd("d", 7, date)M Shasurhttps://www.blogger.com/profile/17390752937266096344noreply@blogger.comtag:blogger.com,1999:blog-28492262.post-64796733161770956062009-05-20T18:38:42.317-07:002009-05-20T18:38:42.317-07:00Good post, but how do you add VB code against a ca...Good post, but how do you add VB code against a calculated field and have that field returned back into the filter and have the logic not see it as a text string.<br /><br />I am using this exact string, but trying to filter the date based on today's date plus seven days.Unknownhttps://www.blogger.com/profile/08874657351055741644noreply@blogger.com