Redimming Arrays in VBA

Question

I have 3 arrays of data that are populated by reading an excel sheet, some of the data points are missing and as such have just been entered into excel as "NA", so I want to look at my array and find each instance of these NA and remove them from the array. because 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 "NA" as one of the values ​​in roe or iCap, I want to get rid of this piece of data in all arrays.

Answer 1

I would not even include “NA” in the first place when building arrays. Here is your code, but modified 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 2

Note. I wrote this code in Notepad.
Let me know if you have any problems 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 3

To be clear, I assume that you have a list of countries in Range C1, and then the associated roe and iCap values ​​in the ranges AP1 and BM1. The problem is that some of the roe deer and iCap are missing and they were entered as "NA". You would like to create arrays that contain only countries that have iBap and ..

First, using Redim Preserve is an “expensive” operation and will affect code performance.

Secondly, as on the sidelines, using syntax like in your code (below) will only result in setting the final variable to String. The first two will be created as variables. Option:

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

This code should be written as:

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

From the point of view of your problem, 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.