0

I have values in an excel file in column C3-CX. That is I have arbitrary numbers of rows. Now I want to read in those values into an array, problem is that every second row is empty and I don't want to include empty space into my array, so I can't do a while loop until a field is empty. How would you solve the issue? Say that I would like to read in the first column into an array. [testa,teste]

    (Col C:2) (Col D:2)
    testa   test2
            test
    teste   test
            test
anders
  • 1,535
  • 5
  • 19
  • 43
  • 2
    [load the spreadsheet into an array first](http://stackoverflow.com/questions/18481330/2-dimensional-array-from-range/18481730#18481730) and then iterate the array backwards and remove empties –  Jul 01 '14 at 09:29
  • can't you sort the range ? That's an easy way to eliminate the empty rows. – iDevlop Jul 01 '14 at 09:43

1 Answers1

0

something like below should work

    Function read_array(inputRange As Range) As String()
    Dim element_count As Integer, i As Integer

    element_count = Application.WorksheetFunction.CountA(inputRange)
    ReDim outArray(1 To element_count) As String
    y = 1
    For i = 1 To element_count

    Do While IsEmpty(inputRange(y))
    y = y + 1
    Loop
    outArray(i) = inputRange(y)
    y = y + 1
    Next
    read_array = outArray
    End Function

you can call this as read_array(c:c) . the output will be a row array.

Heman
  • 118
  • 1
  • 3
  • 8