Arrays issue

[mikejvir]

Hello All,

I hope some else has seen or dealt with this issue. I just can not seem to find a solution. I have a large Excel file that is generated by another program. I am breaking the data up into separate tabs based on one column of data. To do this I need to make sure the other columns only have one value. I was good until this other program does not have a fixed number of columns before data in the columns I need. The first file had 10 columns I had to search, the new file has between 14 and 19 columns. (And no I can not have the other program file modified for a fixed number of columns ).

Someone on this board sent me a module that would determine the number of unique values in an array. Which is working very well. The solution I had before was I predefine variable as Variant for each column and went through a Case structure

DIM ArrayIn as Variant
DIM Col_data01 as Variant
DIM Col_data02 as Variant
. . .
DIM Col_data09 as Variant
DIM UniqueValue as Variant
DIM Uniq_col_items(1 to 9) as long
Dim Nstates as long
. . .
    ArrayIn = ActiveSheet.Range(Cells(row_filter + 1, icol), Cells(RowEnd, icol)).Value
    UniqueValue = UniqueItems(ArrayIn)  ' Subroutine to create Unique items in ArrayIn
    Uniq_col_items(icol) = UBound(UniqueValue)
. . .

      Select Case icol
      Case 1
        Col_data01 = UniqueValue
        flag_stop = True
      Case 2
        Col_data02 = UniqueValue
        flag_stop = True
. . .
      Case 9
        Col_data_State = UniqueValue
        nstates = Uniq_col_items(icol)

Some of the column data could have different values in each index, but these are values that know to be different and I will skip.

I could just increase this number of variables (Col_dataXX) but I am was hoping a 2 dimensional array (variant) could be used. The number of rows is more than 50,000 so I do not want ti define an array that can hold all the data.

Thanks for the help. I hope I explained my problem correctly.

P.S. - I wanted to show part of the Excel file but could not figure out how to do that.

Mike Virostko