Array Join & Filter funcs

[Kevin T. Ryan]

Hi All -

Quick question. I am trying to use VBA's "filter" & "join" functions. I am
able to use the functions ok, but the problem is this: I am trying to load
an array via the

variable_name = ActiveWindow.RangeSelection.Value

method. But this gives me a multi-dimensional array, and the filter and
join functions need a one-dimensional array. Is there a way to extract
*only* one dimension from a multi-dimensional array? I've tried

ReDim Preserve variable_name(UBOUND(variable_name))

but I get an error trying to do this. I know I can create another
temp_array(ubound(variable_name)), and then iterate through each item in
variable_name and assign to temp_array, but I'm wondering if there is a more
elegant/faster solution? Or, is there another quick way to load an entire
range, but only get one-dimensional array?

Thanks for your help!!

Kevin

 

[Tom Ogilvy]

a single row will never exceed 256, so no problem with that. A single
column must be less than or equal to 5461 cells for this to work:

Sub tester9()
Dim varrHoriz
Dim varrVert
On Error Resume Next
varrHoriz = Application.Transpose(Application.Transpose(Range("A1:F1")))
Debug.Print UBound(varrHoriz, 1)
Debug.Print UBound(varrHoriz, 2)
varrVert = Application.Transpose(Range("A1:A10"))
Debug.Print UBound(varrVert, 1)
Debug.Print UBound(varrVert, 2)

End Sub

This will produce a 1D array for a single row, or single column range

 

[Alan Beban]

a single row will never exceed 256, so no problem with that. A single
column must be less than or equal to 5461 cells for this to work:

This limit doesn't apply in xl2000 SR-1 and, I assume, later. In that
version and earlier (and maybe later, I don't know) the 5461 element
limit on the Index function, which Tom may have been thinking of, still
applies.

Alan Beban

 

[Kevin T. Ryan]

Thanks guys, worked like a charm!!

This limit doesn't apply in xl2000 SR-1 and, I assume, later. In that
version and earlier (and maybe later, I don't know) the 5461 element
limit on the Index function, which Tom may have been thinking of, still
applies.

Alan Beban