Cell range starting from region Sheet1.Range("A2") with formulas. "No DATA" string is constant if nothing is pulled as per formula & i don't want it to include in 1-D array while transferring from 2-D array. Once 1-D array is done, then i want to paste 1-D array to "Result" sheet in "B:2" cell & down. I have tried online solutions for it. But arrays are getting complicated for me. New code is also fine. I collected below code from different-different sources.
Sub TwoD_ArrayTo_1D_Array()
Dim rg As Range
'First row is always blank, so started with cell A2 & range contains formula
Sheet1.Range("A2").Select
Set rg = Sheet1.Range("A2").CurrentRegion
Dim arr As Variant, arr1D() As Variant
arr = rg.Value
Dim i As Long, j As Long, k As Long, rows As Long, totalrows As Long
totalrows = (rg.rows.Count - 1) * (rg.Columns.Count)
k = 1
ReDim arr1D(1)
'Convert 2D to 1D array
For i = i To (rg.rows.Count - 1)
For j = 1 To rg.Columns.Count
If arr(i, j).Value = "No DATA" Then 'dont want to copy cell if it contains "No DATA"
GoTo Next_Row
Else
arr1D(k) = arr(i, j)
k = k + 1
ReDim Preserve arr1D(k)
End If
Next_Row: Next j
Next i
'Pasting array values in "Result" Sheet
Dim iRw As Integer
For iRw = LBound(arr1D) To UBound(arr1D)
Result.Cells(iRw, 2).Value = arr1D(iRw, 1)
Next iRw
End Sub
For example here's one approach (assuming there's no problem with making arr2 a 2D array):
Sub TwoD_ArrayTo_1D_Array()
Dim rg As Range, arr As Variant, arr2, v
Dim nr As Long, nc As Long, r As Long, c As Long, i As Long
Set rg = Sheet1.Range("A2").CurrentRegion
arr = rg.Value 'assumes >1 cell...
nr = UBound(arr, 1) '2D array size...
nc = UBound(arr, 2)
ReDim arr2(1 To nr * nc, 1 To 1) 'max possible size needed
i = 0
'loop `arr` and fill `arr2`
For r = 1 To nr
For c = 1 To nc
If arr(r, c) <> "No DATA" Then
i = i + 1
arr2(i, 1) = arr(r, c)
End If
Next c
Next r
'using Resize() only fills the needed range
Sheet1.Range("H2").Resize(i).Value = arr2
End Sub