Multidimensional Array Resizing based on a condition

[arishy]

In my case an VBA Auto generated array resulted in whole outer column(s) of zeros and "outer" row(s) of zeros as well. All what I need is a resized array trimming all the OUTER zeros including zero valued columns. So, if original array 3@5 I could end up with a multidimensional array of 2@3. All the 2@3 have values, but Within these elements zeros can be found.It is the outer zeros that I am interested in cropping.

If a zero column happened to be in the middle of the columns We will KEEP IT.

The reason for this is to preserve the RELATION between the kept columns.

That also applies to middle rows.

By the way I thinks of arrays as Array(rows,columns)

 

[Hui]

Arishy


Can the 0's be on any side of the array? or just the Right and Bottom sides of it ?

 

[NARAYANK991]

Hi ,


See if this helps :

[pre]

Option Base 1

Public Sub Delete_Outer_Rows_Columns()
Dim i As Long, lastrow As Long
Dim j As Integer, lastcol As Integer
Dim data_array As Variant, output_array() As Variant
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("Sheet1").Range("Data_Range")      ' Change as required
data_array = rng.Value
output_array = data_array

For i = LBound(data_array, 1) To UBound(data_array, 1)
For j = LBound(data_array, 2) To UBound(data_array, 2)
If data_array(i, j) <> 0 Then
If i > lastrow Then lastrow = i
If j > lastcol Then lastcol = j
End If
Next
Next

ReDim output_array(lastrow, lastcol)

For i = LBound(output_array, 1) To UBound(output_array, 1)
For j = LBound(output_array, 2) To UBound(output_array, 2)
output_array(i, j) = data_array(i, j)
Next
Next

rng.ClearContents
rng.Resize(lastrow, lastcol).Value = output_array
End Sub

[/pre]
This takes care of only two dimensional arrays.


Narayan

 

[arishy]

Thank you for the "kick start". As you must have realized, the possibilities are endless.

And I have no intention of wasting your time in telling you my test results.So,I will ask you to help me in "forcing" the logic to behave. By that I must give the sub some CONSTANTS, Like which rows are zero sum , and which columns are zero sum ( VBA can calculate this info)and with some logic I can eliminate the OUTER ones only.

In one test case

[pre]

0	0	0	0
0	1	9	0
0	2	8	0
0	3	7	0
0	0	6	0
0	5	5	0
0	6	0	0
0	7	3	0
0	8	2	0
0	9	1	0
0	0	0	0

[/pre]
I can use sum(index(array,r,c)) to do the sum


assume base 1

column 1 and 4

Rows 1 and 11 see test data


Then some VBA to delete these r and c

I am sure there are zillion such ideas, the bottom line is it doable?


One can also think of not deleting anything as you did and just extract the core with the new lBound and UBound.

 

[NARAYANK991]

Hi ,


The code which I have posted is eliminating the outer zeroes ; I assume that outer means the columns to the right and the rows at the bottom.


If you have any other logic , please provide it , so that it can be incorporated in the code.


Narayan

 

[arishy]

Yes, your code did eliminate column 4 and row 11.