+ Reply to Thread
Results 1 to 11 of 11

Edit Recorded Macro to filter column with about 200k rows based on a list of values.

  1. #1
    Registered User
    Join Date
    04-05-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    88

    Edit Recorded Macro to filter column with about 200k rows based on a list of values.

    Hi Guys,

    As you can see from the code, i need to filter out multiple values in a column from the range ("$A$1:$H$195498) , the values to filter is about 718. I tried using the autofilter list to check numbers i need, this usually wont be a problem, but since the numbers are way too large, it is futile.

    I tried inputting manually 718 numbers in the macro as shown in the code below but it errors after 100 numbers.

    I also tried using advanced autofilter, but instead of filtering numbers i need, it came out with zero results.

    Range("A1:H1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$H$195498").AutoFilter Field:=1, Criteria1:=Array( _
    "10016080000160", "10016080018164", "10016080018415", "10016080018804", _
    "10016080018829", "10016080018908", "10016080019000"), Operator:=xlFilterValues

    After recording the macro I tried changing from criteria1:array(_"***","***" and so on to criteria1:range(H2:H718), excel just crashed!.

    Can someone please help me solve this problem.

  2. #2
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Edit Recorded Macro to filter column with about 200k rows based on a list of values.

    Just to clarify, you want to AutoFilter 718 distinct numbers?
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

  3. #3
    Registered User
    Join Date
    04-05-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    88

    Re: Edit Recorded Macro to filter column with about 200k rows based on a list of values.

    Yes, just 718 distinct numbers from the 200k. However, the 718 numbers have duplicates in the 200k. I want the filter to show all the duplicates also.

  4. #4
    Registered User
    Join Date
    04-05-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    88

    Re: Edit Recorded Macro to filter column with about 200k rows based on a list of values.

    Anyone have any idea yet?

    Here with I attached the example file. Filter values in column A using criteria (values) in column B.

    FilterBinA.xlsx

  5. #5
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Edit Recorded Macro to filter column with about 200k rows based on a list of values.

    Hi Rocksan, try it
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-05-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    88

    Re: Edit Recorded Macro to filter column with about 200k rows based on a list of values.

    Nilem

    Wow, your solution is spot on!.

    I have to take time to digest what you just did.

    However, now comes the tough part, since the values have been filtered. I need to pull out the values next to the filtered ones, and copy to another place in the same sheet.

    So the steps are to filter values, pull values next to filtered values to any column on same sheet.

    In the attachment below, filter values in A using the filter criteria at the same time pull out the values in B & C also. So it becomes like this:-

    FilterBinAi.xlsx

    |FILTERED A| B | C|

    Mind though, the data given is only fraction of it, I just need the initial formula, and work on that.

    Please help out.

  7. #7
    Registered User
    Join Date
    04-05-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    88

    Re: Edit Recorded Macro to filter column with about 200k rows based on a list of values.

    Re: Edit Recorded Macro to filter column with about 200k rows based on a list of values.
    Nilem

    Wow, your solution is spot on!.

    I have to take time to digest what you just did.

    However, now comes the tough part, since the values have been filtered. I need to pull out the values next to the filtered ones, and copy to another place in the same sheet.

    So the steps are to filter values, pull values next to filtered values to any column on same sheet.

    In the attachment below, filter values in A using the filter criteria at the same time pull out the values in B & C also. So it becomes like this:-

    FilterBinAi.xlsx

    |FILTERED A| B | C|

    Mind though, the data given is only fraction of it, I just need the initial formula, and work on that.

    Please help out.

  8. #8
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Edit Recorded Macro to filter column with about 200k rows based on a list of values.

    Rocksan, see attached file
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    04-05-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    88

    Re: Edit Recorded Macro to filter column with about 200k rows based on a list of values.

    Nilem

    I cant download the attached file due to restrictions at my place. Could you please post the codes only like you did the first one. Thank you.

  10. #10
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Edit Recorded Macro to filter column with about 200k rows based on a list of values.

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    04-05-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    88

    Re: Edit Recorded Macro to filter column with about 200k rows based on a list of values.

    Nilem,

    You single handedly solved the problem. Thank you very much. I will now close the thread.

    Thanks again. Rep for you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1