VBA Array - is there a quick way to export an entire row of a 2 dimensional array to a worksheet range?

[Mike UK]

Hi,
I have a two dimensional array. The code checks a row element (column 9).

If it contains "Include" then I want the entire array row on one worksheet.
If it doesn't contain include then the entire row is placed on another worksheet.
It then goes through each row in the array checking column 9 outputting as defined.

Is there a quick way to output the entire row rather than cell by cell? The two variants of code below don't work.

Sub ExportArray()

Dim OutputArray As Variant
Dim BDMRow As Integer
Dim NatRow As Integer
Dim i As Integer

RowCount = Sheets("Combined").Range("A" & Rows.Count).End(xlUp).Row
OutputArray = Sheets("Combined").Range("A1:O" & RowCount)

BDMRow = 2
NatRow = 2

For i = 2 To UBound(OutputArray, 1)

If OutputArray(i, 9) = "Include" Then
Sheets("BDM").Range("A" & i & ":O" & i) = OutputArray.Range(Cells(i, 1), Cells(i, 15))
BDMRow = BDMRow + 1
Else
Sheets("Nationals").Range("A" & i & ":O" & i) = OutputArray.Cells(i, 1).Value
NatRow = NatRow + 1
End If

Next


End Sub

 

[Norie]

Have you tried using Application.Index?

Sub ExportArray()
Dim OutputArray As Variant
Dim BDMRow As Long
Dim NatRow As Long
Dim i As Long

    RowCount = Sheets("Combined").Range("A" & Rows.Count).End(xlUp).Row
    OutputArray = Sheets("Combined").Range("A1:O" & RowCount)
    
    BDMRow = 2
    NatRow = 2
    
    For i = 2 To UBound(OutputArray, 1)
    
    If OutputArray(i, 9) = "Include" Then
        Sheets("BDM").Range("A" & BDMRow & ":O" & BDMRow) = Application.Index(OutputArray, i)
        BDMRow = BDMRow + 1
    Else
        Sheets("Nationals").Range("A" & NatRow & ":O" & NatRow) = Application.Index(OutputArray, i)
        NatRow = NatRow + 1
    End If
    
    Next i

End Sub

 

[Mike UK]

Thanks Norie, I'm new to Arrays so stumbling my way around a bit. Your code worked perfectly. Thank you very much.

 

[Mike UK]

Thanks Norie, I'm new to Arrays so stumbling my way around a bit. Your code worked perfectly. Thank you very much.

My Array has 18,000 rows..... would it be quicker to write to two separate arrays then output each array in total to the relevant worksheet or leave as is and write to each worksheet as it moves through the rows?

 

[Fluff]

Writing the output to separate arrays would be quicker than constantly writing to the sheet.
Another option would be to autofilter & copy.

 

[Mike UK]

Thanks Fluff, and great idea to use filter.