+ Reply to Thread
Results 1 to 5 of 5

filter a 2d array

  1. #1
    Registered User
    Join Date
    09-10-2007
    Location
    Texas, USA
    MS-Off Ver
    Office 365 (2016)
    Posts
    95

    filter a 2d array

    i have an array from a range selection, now i want to filter that array to create a new array based on my criteria...and also changed the values in one column.

    arr = ActiveSheet.Range(selection.Address).Value

    Point Chainage[mi] CRdc[mm/year]
    [L0026_BH_SM_GW_201] 0 0.82
    [L0026_BH_SM_GW_541] 1.2 0.53
    [L0026_BH_SM_GW_863] 1.8 1.32
    [L0026_REC_BH-700-CR-102BH] 2.1 2.54
    [L0026_REC_BH-700-CR-102BH] 2.9 6.81
    [L0026_BH_SM_GW_1835] 3.7 0.48
    [L0026_BH_SM_GW_26332] 4.2 1.97

    for the new array of values, i want to get rid of any row (in the range, NOT entire row) that doesnt contain "_GW_" in the first column. Then I would like to strip out some text in the first column of the array such that only the number after "_GW_" remains:

    [L0026_BH_SM_GW_541] would be 541
    [L0026_BH_SM_GW_26332] would be 26332

    Hoping there is a simple method for this that doesnt require a bunch of code. Also dont want to use autofilter.

  2. #2
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,158

    Re: filter a 2d array

    Try it:
    Please Login or Register  to view this content.
    Artik

  3. #3
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: filter a 2d array

    I think there may be simpler code possible, but what that code is depends on what you are actually trying to do in the end. Are you wanting a two-dimensional VBA array for use in further code or are you wanting to output the filtered data to a worksheet range once created? Can you clarify your ultimate intent for us?

  4. #4
    Forum Expert
    Join Date
    10-11-2021
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    1,367

    Re: filter a 2d array

    As Rick already mentioned. It could be done with simpler code

    Please Login or Register  to view this content.
    Or with Excel basics

    Please Login or Register  to view this content.
    Last edited by JEC.; 06-29-2022 at 09:33 AM.

  5. #5
    Registered User
    Join Date
    09-10-2007
    Location
    Texas, USA
    MS-Off Ver
    Office 365 (2016)
    Posts
    95

    Re: filter a 2d array

    Artik,
    thank you so much for this. It did what i needed it to do, now i just have to study it so i can learn from it!

    Rick,
    I am intrigued if you think you can simplify the code. Basically, my intent is to replace the selected values with the new filtered values, so something like this to the end of Artik's code:

    selection.ClearContents
    selection.Resize(UBound(varrOutput, 1), UBound(varrOutput, 2)).Value = varrOutput

    also, now i am seeing that the user may likely include the header in their selection which would end up getting filtered out. Is there a way to check if the header is selected when the header values are not predictable (other than a msgbox)? i guess i could modify my code that populates the sheet to begin with so that it is formatted in such a way that currentregion would work to get the range, instead of selection, and then the first row could always be assumed to be part of the range. unless you know of a way to check for it? your thoughts?


    One other thing that i have never attempted, or even looked into, is building in the use of undo after initial run. is that even possible? would be nice to get the original values back with one undo click.

    [EDITED]
    JEC,
    Just saw your post after i posted this. i will give your code a try now.
    [EDITED2]
    JEC,
    the first bit of code didnt work for me because it uses currentregion, which my data is not currently set up for. i tried to change to selection, but then it didnt write out properly.
    the second bit of code worked great when i changed the "With" to be selection, however this just satisfies the filter portion of my request. Now it would need to do the replacement of values in the first column. BETTER YET...leave those values alone and add an entire new column next to the selection and add the new values there, making the new array 4 columns wide.
    Last edited by Coley356; 06-29-2022 at 10:29 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Index and Filter Functions: Workaround to sort, filter, and format within array?
    By beginner21 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-25-2021, 04:48 PM
  2. [SOLVED] Filter Array 1 with Array 2 as criteria, returning filtered data to source sheet
    By nigelog in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-31-2019, 01:08 PM
  3. [SOLVED] Filter without use of array?
    By mrsak87 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-05-2018, 09:28 AM
  4. Filter in array 2D
    By daboho in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-30-2017, 04:05 PM
  5. Array VBA Filter
    By rbirch in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-11-2016, 09:20 PM
  6. Replies: 6
    Last Post: 05-26-2012, 04:56 AM
  7. filter out the array
    By kaffal in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-29-2009, 02:06 PM

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