1. 5

    Speed up an Autofilter with 20k+ rows

    Hey everyone. Hope you're all doing well. I have a listObject table with 20k+ rows that I'm filtering with a textbox. This is the code I modified a little from extendoffice: Private Sub TextBox1_Change() 'Update by Extendoffice 2018/08/22 Dim xStr, xName As String Dim xWS As Worksheet Dim xRg...
  2. T

    Filter Geography (rich data type) columns using multiple SubField parameters via VBA

    I am trying to figure out how the new rich data type fields (Geography and Stocks) work with respect to auto filters and VBA. For example, suppose I have a table with header "City" and five cities: "New York", "Los Angeles", "San Francisco", "Toronto" and "Boston". The cities have the...
  3. R

    VBA Autofilter not working! (3 criteria Array)

    Calamity! Have this autofilter: Range("A1:W" & Lastrowaf1).AutoFilter field:=12, Criteria1:=Array(bkdate, bkdate2, bkdate3), Operator:=xlFilterValues ' Booking Date Lastrowaf1 is working (12,000 rows), field 12 is the autofilter field (contains date as serial, 44242 etc) bkdate, bkdate2...
  4. H

    How to stop RE on autofilter VBA macro

    I have a number of VBA macros which autofilter, hide certain columns, then copy the results, minus the header row, to another sheet. By and large they work well, but when there is nothing to copy, there is generally a RE on the line indicating the destination sheet Here is just one of the...
  5. A

    Copy rows onto another worksheet based on multiple criteria (dynamic criteria)

    I'm trying to copy rows from one sheet to another worksheet based on a number of criteria (currently from a dropdown). I am currently able to do this for non-dynamic (non-changing) criteria using either an excel formula or VBA coda (which is much faster). However, how can I set up my macro so...
  6. P

    AutoFill Formula from ActiveCell till the last cells of other columns having data.

    Hi there, My Name is Prathik. I'd like to apologize if this question has already been answered. I didn't find anything about this anywhere on the internet. So posting it here. Please assist. So I'm writing this code wherein my macro writes a formula in E2 (Range A To D already has data). Then...
  7. V

    Write to First Visible Cell after autoFilter in VBA

    Hello Have a great day, I am very new to Macros and want to create a dynamic macro and automate my day to day task . I wrote code to copy data from other workbooks and append it on other workbook(testmac) I applied a filter and select the All amount which are greater than Zero in Column K Now...
  8. D

    Excel VBA AutoFiltering using InputBox to specify the Criteria - *contains*

    Hi Folks, My goal is to be able to filter a column in a dynamic table for strings that contain non-alpha characters like #, $, etc using wildcards (*). I wish to use an InputBox to specify the special character. Something unexpected is happening: If I directly code 'Criteria1' e.g., for #...
  9. J

    VBA filtering array

    Hi All I'm looking to filter with the criteria being an array - can't quite get it to work. Simple example below - where am I going wrong? Thanks in advance! Jon Sub test() Dim Narrative As Variant Narrative = Array("*OPENING*", "*CLOSING*", "*VALUE*") Range("A2:H100").AutoFilter FIELD:=4...
  10. L

    Need to bring Cell value into autofilter VBA

    Hello Everyone, I have the following code currently: ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=33, Criteria1:= _ "<7/1/2020" ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=35, Criteria1:= _ "<1/01/1900"...
  11. U

    If criteria1 does not exist then...

    Hi all, This is a pretty common question but I can't seem to find a solution that works for me. I have the following code that is supposed to apply autofilter but if the criteria does not exist, I want it to turn off the filter and end if. However, the line If currentBoxRange.Offset(-1...
  12. H

    Autofilter More Than 2 Items

    I have a VBA macro which does a number of things, but I have not been able to get it to autofilter for more than 2 things, but I need it to do 4 Here is the macro Sub Low_Risk_Lays() ' ' Low Risk Lays Macro ' This macro will filter for Remove VDW Rank 1, Class, Distance <=1650, # of Runners...
  13. J

    VBA - filtering columns after inserting or deleting cells

    Good afternoon I'm looking for a way to make the field of an autofilter dynamic - for example: Range("a1:J" & LRow ).autofilter Field:=7, Criteria1:="" If someone were to add a column between B&C, the field would now need to be 8, but the code won't register this. I've thought about maybe...
  14. T

    Filter loop through macro

    I have various workbooks that I need to check the data in by filtering on various columns to find those that need to be checked and then I filter through the resultant list using usually column 'C', checking line by line and marking those 'done'. What I'd like to achieve is a button that...
  15. MikeMcCollister

    AdvancedFilter in VBA, Help!

    I am trying to do some advanced filtering so that I can sum some columns. My test code does not work. First when I try to set the criteria cell VBA just errors out. If I skip that and try to Call rangeTable.AdvancedFilter it does nothing. Function FilterTest(theMonth As String, category As...
  16. M

    VBA code to auto filter using cell reference

    Hi, I need a VBA code to autofilter list of values reference from a different sheet. The filter condition is such it should contain the values in the referenced cells not equal to the value in the cell The code I have been trying is as below Worksheets("ZBA extract").select...
  17. H

    Autofilter for Top Item

    Hi I know with autofilter it is possible to filter for top or bottom 10, but can't see any way to filter for the best item in a list. I have a sheet with maybe 150k rows and the data is grouped into dates, times and names and many columns with numbers relating to particular information. I'd...
  18. P

    Range.Insert not working when filter is active

    I haven't noticed this issue in regular Excel because I have an active filter, I can still right-click and insert a new table column. However, periodically, when I try to insert a new column in VBA, I run into a run-time 438 error when I have an active filter. Has anyone else experienced this...
  19. A

    macro, filter criteria2 = array for date column

    a macro reads the filtering selected by the user of an excel data file. When the user selects from a date column, using the tree list to select years, I can record a macro that simulates what has been selected as: ActiveSheet.Range("$A$3:$T$179896").AutoFilter Field:=4, Operator:= _...
  20. M

    Simple VBA Amendment

    Hello all, A really simple one for you folk I'm sure. I've got some existing code which I need to adapt ever so slightly. 1) The code currently runs on all sheets except 'Summary', but I need it to run on all sheets except 'Summary' and 'FAQs' 2) The other tabs have an autofilter on row 4...

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back