Remove Blank Entries From a 2-D Array

[Altec103]

Hello -

Thank you in advance for any help regarding this topic.

I have a 2-D array initialized in my code with variable row and column counts. Within that array there are rows which begin with an empty entry. I cannot modify the input, so I am forced to remove the rows which begin with an empty entry from the array.

My first attempt at a solution I created a second 2-D array with the same dimensions as the first 2-D array. I then used nested for-loops to traverse the first array and used an if statement to only add to the second array if the entry was non-empty. I thought this would work, but it still had the empty rows.

Please Login or Register  to view this content.

Any help on this would be greatly appreciated. I'm surprised I could not find a cookie cutter function online. This just seems like an exercise in traversing an array and removing entries, but I am struggling.

Thank you.

EDIT: To clarify a potential question, if a row in the array begins with an empty entry then the entire row will be empty.

EDIT2: By the way this is all being done with an Excel VBA sub with a 2-D variant array.

[jindon]

Which do you want?

Remove or empty?

[Altec103]

Which do you want?

Remove or empty?

The blank entries in the array are already empty. I want to remove them. Below would be a small example of a 4 by 3 array:

3 7 1
- - -
- - -
2 9 7

(where the dashes represent blank entries. After removal the array would be 2 by 3)

3 7 1
2 9 7

Note I want to remove the entire row which begins with a blank entry, not just the individual cell.

[jindon]

This will remove the row if the first element of each row is empty

Please Login or Register  to view this content.

[xlnitwit]

A simple method would be to loop the rows of the array and count the non-blank entries. That then gives you the size for the second array which you populate in a second loop like you have currently.
Alternatively you might populate the second array in transposed fashion, using a counter for the row to fill, so that you can use Redim Preserve to resize it at the end and then transpose it back.

[Altec103]

Hi Jindon -

Thank you, this worked. However, there is one issue. This seems to produce trailing N/As at the end of the new array (one N/A row for each empty row removed). Any ideas what might be causing this?

[Altec103]

Hi xlnitwit -

Thanks for the answer. I will try the counting method as it looks like Jindon has provided an answer using the transpose method.

Out of curiosity, any idea which method would be less computationally complex? I will be working with over 10,000 rows, so I do want to keep performance in mind.

[xlnitwit]

Redim Preserve is quite an expensive operation as it creates a copy of your array each time. Rather than doing that in a loop, I would prefer to do it once at the end after calculating the actual count. What are you actually doing with the array once you have removed the blank rows?

[Altec103]

I attempted to follow your instructions and created a 2nd array with new dimensions. But I am having trouble getting it filled without throwing an indices error. Below is my second attempt:

Please Login or Register  to view this content.

Also, what do you think of Jindon's answer? It works with the exception of the trailing N/As which I'm hoping he can debug because I wasn't able to fix. And I'm just writing the array to the sheet after removing the blanks. Maybe I can solve it there? Below is where I display the array:

Please Login or Register  to view this content.

[jindon]

Hi Jindon -

Thank you, this worked. However, there is one issue. This seems to produce trailing N/As at the end of the new array (one N/A row for each empty row removed). Any ideas what might be causing this?

Can not replicate the issue, if you post your full code, it would help.

[jindon]

Should be

Please Login or Register  to view this content.

[xlnitwit]

Your issue isn't with jindon's code, it's the fact that you are sizing the range based on the original array dimensions. You should use the Ubound of the new array to size it.

Edit: i.e. what jindon said in post #11!

[xlnitwit]

I attempted to follow your instructions and created a 2nd array with new dimensions. But I am having trouble getting it filled without throwing an indices error.

You need a counter variable to populate the second array

Please Login or Register  to view this content.

[jindon]

Altec103

How are you populating initial array?

[bakerman2]

I will be working with over 10,000 rows, so I do want to keep performance in mind.

This one took 0.04 sec. to filter 5030 filled rows out of 10.000 rows array.

Please Login or Register  to view this content.