+ Reply to Thread
Results 1 to 8 of 8

VBA - Array - Select Ranges Based Upon Criteria

  1. #1
    Registered User
    Join Date
    04-26-2019
    Location
    Australia
    MS-Off Ver
    365 ProPlus
    Posts
    13

    VBA - Array - Select Ranges Based Upon Criteria

    Hi,

    I am trying to use an array sub to run through a table of data, pick out the rows that meet a certain criteria and then unload that summary data to another table.

    I have attached a very simple table and example to show the before/after scenario. The number of rows to be read is dynamic, not set.
    I have created a COUNTIF cell in L6 to help me get the size of the output array. If there is a way to not include that step then that would be most appreciated.

    I am also not sure if the range has to be selected as a whole then needs to be split to be placed in the respective cells in the final table.
    I have multiple versions of what I think could have worked but obviously none have so I apologise for the dogs breakfast below. I just want to show that I at least tried

    But basically it is:

    HTML Code: 
    Many thanks in advance.
    (PS. I am struggling to attach the file. I will log out and try again)

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,803

    Re: VBA - Array - Select Ranges Based Upon Criteria

    To attach a sample workbook (not a picture or pasted copy):
    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    04-26-2019
    Location
    Australia
    MS-Off Ver
    365 ProPlus
    Posts
    13

    Re: VBA - Array - Select Ranges Based Upon Criteria

    Thanks Alan, I tried to do that initially. I selected the file, it showed on the page, I then clicked "Upload File" and haven't seen it since. After logging out and then back in again and repeating the process it still hasn't appeared. Is there a step I missed?

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,446

    Re: VBA - Array - Select Ranges Based Upon Criteria

    Before anything, you need to re-dimension the array with ReDim keyword before you use.

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Lightbulb

    Hi !

    To copy data from a place to another place, an easy way is an Excel basics like an advanced filter …

  6. #6
    Registered User
    Join Date
    04-26-2019
    Location
    Australia
    MS-Off Ver
    365 ProPlus
    Posts
    13

    Re: VBA - Array - Select Ranges Based Upon Criteria

    Jindon, thanks. On an early version of this I did include the ReDim then I ditched it. I will include it again.

    Marc L. That was something that we initially thought of but we tried to lessen the manual steps involved. The practical reality of this exercise is that we will be receiving a couple of hundred of these templates from various sources and the actual list will be around 800 rows long by 30 columns wide. We were hoping to get this down to a 'push the button' solution (which I know can also be managed by filtering). If all else fails then we might resort back to that option.

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,446

    Re: VBA - Array - Select Ranges Based Upon Criteria

    Something like this
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    04-26-2019
    Location
    Australia
    MS-Off Ver
    365 ProPlus
    Posts
    13

    Re: VBA - Array - Select Ranges Based Upon Criteria

    Thanks Jindon,

    that is exactly what I am after. I will use that as the base for the actual larger data set.
    I need to really get my head around ReDim.

    Most appreciated

+ 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] VBA to select all ranges stored in array at once
    By sintek in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-27-2017, 03:50 AM
  2. [SOLVED] listing array values in a dynamic list based on 2 criteria from ranges
    By lkeltner in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-14-2016, 01:45 PM
  3. [SOLVED] How do I select and copy ranges of about 5000 rows based on sets of criteria?
    By dasseya1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-05-2013, 10:03 PM
  4. How do I select and copy ranges of indefinite numbers of rows based on sets of criteria?
    By patricia hatton in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-02-2013, 10:11 AM
  5. Select Array Based on Criteria
    By excelsf in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-21-2010, 06:04 PM
  6. Select items based on criteria and return in an array
    By apolloni in forum Excel General
    Replies: 4
    Last Post: 07-02-2010, 07:31 AM
  7. [SOLVED] select variables ranges, copy to array, paste the array in new workbook
    By Mathew in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-01-2005, 05:06 AM

Tags for this Thread

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