Writing an array back to the sheet.

[JeffGrant]

Hi All,

I am just learning about arrays.

I have this code that reads the initial array in and joins several columns.

Sub AddColumns()
Dim arr As Variant, rng As Range
arr = Sheet61.Range("A1").CurrentRegion

Dim i As Long

    Set rng = Sheet61.Range("L1")

    With rng.Borders
        rng.Value = "UniqueID"
        rng.Font.Bold = True
        rng.BorderAround xlContinuous
        rng.Borders(xlInsideHorizontal).LineStyle = xlContinuous
        rng.Borders(xlInsideVertical).LineStyle = xlContinuous
    End With

For i = LBound(arr, 1) + 1 To UBound(arr, 1)
    arr(i, 12) = arr(i, 3) & arr(i, 7) & arr(i, 9)
Next i

End Sub

where i need help is how to write the array out again with the extra column. I can do it if i put the write statement inside the For Loop, but that does give me any speed advantage.

I think it has something to do with resizing the initial array, but I am not sure how to go about that.

Thanks for your help.

 

[HighAndWilder]

Hi All,

I am just learning about arrays.

I have this code that reads the initial array in and joins several columns.

Sub AddColumns()
Dim arr As Variant, rng As Range
arr = Sheet61.Range("A1").CurrentRegion

Dim i As Long

    Set rng = Sheet61.Range("L1")

    With rng.Borders
        rng.Value = "UniqueID"
        rng.Font.Bold = True
        rng.BorderAround xlContinuous
        rng.Borders(xlInsideHorizontal).LineStyle = xlContinuous
        rng.Borders(xlInsideVertical).LineStyle = xlContinuous
    End With

For i = LBound(arr, 1) + 1 To UBound(arr, 1)
    arr(i, 12) = arr(i, 3) & arr(i, 7) & arr(i, 9)
Next i

End Sub

where i need help is how to write the array out again with the extra column. I can do it if i put the write statement inside the For Loop, but that does give me any speed advantage.

I think it has something to do with resizing the initial array, but I am not sure how to go about that.

Thanks for your help.

Can you please post the appropriate data using XL2BB so that we can see the size of the initial array?

 

[JeffGrant]

Unfortunately I cant. I have always had trouble with the XL2BB add-in. It just messes up my machine and I have never been able to solve the issue. Sorry

 

[JeffGrant]

Figured it out. Instead of writing out an extra column to the initial array, I wrote the whole array out to another location and everything worked.