Swapnil Supekar's questions

单元格范围从区域 Sheet1.Range("A2") 开始，并带有公式。如果没有按照公式提取任何内容，则“无数据”字符串是常量，并且我不希望在从二维数组传输时将其包含在一维数组中。完成一维数组后，我想将一维数组粘贴到“结果”表中的“B:2”单元格中并向下粘贴。我尝试过在线解决方案。但数组对我来说越来越复杂。新代码也很好。我从不同的来源收集了以下代码。

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