Filtering array in memory "does not equal" values and delete those rows

LlebKcir

Board Regular
Joined
Oct 8, 2018
Messages
206
copy column data from one array to another - Code to create colSortedArr fully functional

My google/duckduckgo foo have utterly failed me this morning. Everything I am finding has to do with .AutoFilter on a worksheet. NOT what I need, will not do the job.

2 workbooks:
1 = Master multiple tables with 100+ identical columns in each table.
2 = Comm, with 1 table having 41 column headers that use the exact same column name as the tables from the Master.

As a temp. process of learning, I have successfully made an array from 1 hard coded table in the Master to build an array that is in memory for the Comm containing the columns with their full data.

This full data will, in some cases, be more data than is required. I now need to filter down the data in the Comm array floating out there in memory by the sub-network and delete all rows that do not match the sub-network criteria. Followed by deleting any rows that are empty based on the group column.

The deleting of rows within array searches have also pointed me to ONLY deleting rows within a worksheet, not within an array in memory.

Do I?
1. use some form of filter?
2. loop through the colSortedArr to find the sub-network I am looking for and somehow copy that row to a new array?
2a. if I do #2, do I need to copy the column headers over?
3. other options?

Desired outcome. Data in colSortedArr is 1st filtered on City/Network either !=, or = sub-network and delete all rows that are unwanted. 2nd filter remaining data on the group column if any cells in that column are blank, delete that entire row. 3rd paste remaining data into the Overview2 worksheet in the Comm workbook.
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,751
Not really filtering arrays. Loop your original array through whatever "filter" requirements U have and create a new array of the "filtered" contents. Here's an example of creating an array of unique values from the original array...
HTH. Dave
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,751
Never really mentioned how to operate that code which is probably important to U...
Code:
Dim NewArray() as Variant, OrigArray() as Variant
NewArray = UniqueArr(OrigArr)
The point being that U pass the array to the function, the function manipulates the array and then returns a new array which it seems is what you're trying to achieve. HTH. Dave
 

LlebKcir

Board Regular
Joined
Oct 8, 2018
Messages
206
NdNoviceHIp, that link I am not seeing what you used as the filter in the code?
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,751
You are correct. My suggestion is that there is no need to use XL's filter... create your own criteria by code. The code just provides an example of how to manipulate an existing array to create a new array. HTH. Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,126,945
Messages
5,621,764
Members
415,855
Latest member
John McCuaig

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 MrExcel.com.
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 "mrexcel.com".
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
Top