Thread: Working with Array Data

I have a macro that manipulates a rather large amount of date. To speed up the process, I use a 2 dimensional array loaded from the data on an excel sheet rather than manipulating the data directly in the excel sheet. After all calculations are complete, I then write back the entire chunk of data to the worksheet.

All of this works great, and it does speed up calculations....

There are a few "columns" of data that are not needed on the final output, and there are some "rows" that need to be deleted as well. Currently I am writing the entire array back to the excel sheet and another subroutine goes through and cleans up the data (deletes selected columns and rows)

My question is: Is there a way to delete this un-needed data while it is still in an array (effectively resizing the array - removing specific columns and rows) and then writing the array back to the worksheet?

Example:
(5X5) Array with the following data

Code:

A A X A A
B B X B B
X X X X X
D D X D D
E E X E E

Delete Column 3 and Row 3 Leaving

Code:

A A A A
B B B B
D D D D
E E E E

And writing that back to the worksheet

The main reason I am asking is curiosity - my subroutines function fine as is, but I just cant seem to leave them alone - always tweaking and trying to be more efficient.

TIA for any comments

-Lee

You would have to copy the array down over the replaced row, and then resize the array (Redim Preserve ...). Whether that's faster is something you could determine experimentally.

I was always under the impression you could not ReDim Preserve a multi dimensional array, or rather you could only ever ReDim Preserve the final dimension of said array. Not something I know much about mind you...