filling 2D array with no-zero values

[Greg]

Hi,

I have a 2D array with variable # rows and 2 columns as follows:

n = inputy.Cells.Count
k = Application.WorksheetFunction.CountIf(inputy, ">0")

Dim arrayXY() As Variant
ReDim arrayXY(1 To k, 1 To 2) As Variant

I need some help on the code enabling me to fill it with non-zero values
(going into the second column). The input range could have 0 to k-1 zero
values.

So if for instance, out of 8 cells in the range “inputy†, two could have
zeros, so my array should be 6 x 2 , excluding those zeros.

Thank you for your help
Regards,
Greg

 

[RyanH]

I think this should work for you. Minor change.

k = WorksheetFunction.CountIf(inputy, "<>0")

Hope it helps.

 

[Greg]

hi Ryan,
"k" works fine for me. I'm having trouble filling an array
for example
Range inputx is in A1: A8 having 1,2,3,4,5,6,7,8
Range inputy is in B1:B8 having 0,4,0,16,25,36,0,64

i want an 5 by 2 array having the following elements
arrayXY(1,1) = 2, arrayXY(1,2) = 4;
arrayXY(2,1) = 4, arrayXY(2,2) = 16;
arrayXY(3,1) = 5, arrayXY(3,2) = 25;
arrayXY(4,1) = 6, arrayXY(4,2) = 36;
arrayXY(5,1) = 8, arrayXY(5,2) = 64;

 

[RyanH]

I'm not sure if you can do that. I wrote some code that could get you the
result you need in a different way though. You will have to adjust it to
your specifications but it should work. I basically scanned the range and
made a new range next to it, then I setup your Array. I hope this helps!

Sub SetArray()

Dim LastRow As Long
Dim myRange As Range
Dim i As Long
Dim NextRow As Long
Dim k As Long
Dim ArrayXY() As Variant

LastRow = Sheets("Sheet1").Cells(Rows.count, "B").End(xlUp).Row

For i = 1 To LastRow
If Cells(i, 2) <> 0 Then
NextRow = NextRow + 1
Range(Cells(NextRow, 4), Cells(NextRow, 5)).Value = Range(Cells(i,
1), Cells(i, 2)).Value
End If
Next i

k = Sheets("Sheet1").Cells(Rows.count, "D").End(xlUp).Row

ReDim ArrayXY(1 To k, 1 To 2) As Variant

'the rest or your code here

End Sub