Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Join & Filter funcs
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Join & Filter funcs
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 -- Regards, Tom Ogivy "Kevin T. Ryan" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Join & Filter funcs
Tom Ogilvy wrote:
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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Join & Filter funcs
Thanks guys, worked like a charm!!
"Alan Beban" wrote in message ... Tom Ogilvy wrote: 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
Filter an array | Links and Linking in Excel | |||
Join tables like inner join in Access | Excel Discussion (Misc queries) | |||
Conditional filter using array formula | Excel Worksheet Functions | |||
Conditional filter using array formula | Excel Worksheet Functions |