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

Answer 2

I wouldn't even include the "NA" in the first place when building the arrays. Here's your code, but changed to not include "NA".

Sub group_data()

Dim country() As String
ReDim country(0)
Dim roe() As String
ReDim roe(0)
Dim iCap() As String
ReDim iCap(0)

Dim i As Integer
Dim increment1, increment2, increment3 As Integer
increment1 = 0
increment2 = 0
increment3 = 0

For i = 1 To 3357
    If Not Workbooks("restcompfirm.xls").Worksheets("Sheet1").Range("C1").Offset(i, 0) = "NA" Then
        ReDim Preserve country(UBound(country) + 1)
        country(increment1) = Workbooks("restcompfirm.xls").Worksheets("Sheet1").Range("C1").Offset(i, 0)
        increment1 = increment1 + 1
    End If

    If Not Workbooks("restcompfirm.xls").Worksheets("Sheet1").Range("AP1").Offset(i, 0) = "NA" Then
        ReDim Preserve roe(UBound(roe) + 1)
        roe(increment2) = Workbooks("restcompfirm.xls").Worksheets("Sheet1").Range("AP1").Offset(i, 0)
        increment2 = increment2 + 1
    End If

    If Not Workbooks("restcompfirm.xls").Worksheets("Sheet1").Range("BM1").Offset(i, 0) = "NA" Then
        ReDim Preserve iCap(UBound(iCap) + 1)
        iCap(increment3) = Workbooks("restcompfirm.xls").Worksheets("Sheet1").Range("BM1").Offset(i, 0)
        increment3 = increment3 + 1
    End If

Next i

End Sub

Answer 3

Just to be clear, I am assuming you have a list of countries in Range C1 and then associated roe and iCap values in ranges AP1 and BM1. The issue that some of the roe and iCap entires are missing and have been entered as 'NA'. You would like to create arrays that contain only countries where there is both an roe and iCap value.

Firstly, using Redim Preserve is an 'expensive' operation and will impact efficiency of code.

Secondly, as an aside, using syntax as in your code (below) will only set the final variable to String. The first two will be created as variable type Variant:

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

This code should be written as:

Dim country() as String, roe() as String, iCap() As String 

In terms of your issue, my approach would be as follows:

Sub FillArrays()
'Define arrays
Dim countryArray() As String, roeArray() As Variant, iCapArray() As Variant

'Get total number of countries
Dim totalRows As Long
totalRows = Workbooks("restcompfirm.xls").Worksheets("Sheet1").Range("C1").End(xlDown).Row

'Define array size based on totalRows
ReDim countryArray(totalRows - 1)
ReDim roeArray(totalRows - 1)
ReDim iCapArray(totalRows - 1)

'Define missing data text
Dim missingData As String
missingData = "NA"

Dim iArray As Long
iArray = 0

With Workbooks("restcompfirm.xls").Worksheets("Sheet1")

'Loop through each row and check if either roe or iCap are set to 'NA'
For cl = 1 To totalRows

    If Trim(.Range("AP" & cl)) <> missingData Then
        If Trim(.Range("BM" & cl)) <> missingData Then

            countryArray(iArray) = .Range("C" & cl)
            roeArray(iArray) = .Range("AP" & cl)
            iCapArray(iArray) = .Range("BM" & cl)

            iArray = iArray + 1
        End If
    End If

Next cl

End With

End Sub

Hope this helps.