Redimming arrays in VBA

Question

I have 3 arrays of data, that are filled by reading off of an excel sheet, some of the points of data are missing and as such have just been entered into excel as "NA" so I want to look through my array and find each instance of these NA's and remove them from the array since the information is useless. I need to update all three arrays at the same time.

Sub group_data()
    Dim country(), roe(), iCap() As String
    Dim i As Integer
    For i = 1 To 3357
        country(i) = Workbooks("restcompfirm.xls").Worksheets("Sheet1").Range("C1").Offset(i, 0)
        roe(i) = Workbooks("restcompfirm.xls").Worksheets("Sheet1").Range("AP1").Offset(i, 0)
        iCap(i) = Workbooks("restcompfirm.xls").Worksheets("Sheet1").Range("BM1").Offset(i, 0)
    Next i 
End Sub

So if I find a "NA" as one of the values in roe or iCap I want to get rid of that piece of data in all there arrays.

Answer 1

Note: I have written this code in Notepad.
Let me know if you face any problem with this.

Sub group_data()
dim totalRows as integer
dim rowNum as integer
dim rowsWithoutNA as integer

dim c1Range as Range
dim ap1Range as Range
dim bm1Range as Range

set c1Range = Workbooks("restcompfirm.xls").Worksheets("Sheet1").Range("C1")
set ap1Range = Workbooks("restcompfirm.xls").Worksheets("Sheet1").Range("AP1")
set bm1Range = Workbooks("restcompfirm.xls").Worksheets("Sheet1").Range("BM1")


Dim country(), roe(), iCap() As String
Dim i As Integer

totalRows = 3357

redim country(totalRows)
redim roe(totalRows)
redim iCap(totalRows)

For i = 0 To (totalRows - 1)
   rowNum = rowNum + 1

   roe(rowsWithoutNA) = ap1Range.Offset(rowNum, 0).Text
   iCap(rowsWithoutNA) = bm1Range.Offset(rowNum, 0).Text

   if (WorksheetFunction.IsNA(roe(rowNum)) _
      OR WorksheetFunction.IsNA(iCap(rowNum))) = False Then
   ' use the following condition, if NA is written in text 
   'if (trim(roe(rowNum)) = "NA" OR trim(iCap(rowNum)) = "NA") Then
      country(rowsWithoutNA) = c1Range.Offset(rowNum, 0)
      rowsWithoutNA = rowsWithoutNA + 1
   end if
Next i 

redim preserve country(rowsWithoutNA )
redim preserve roe(rowsWithoutNA )
redim preserve iCap(rowsWithoutNA )

end sub