Write text to multiple cells using variants

Hey there,

I have created a sheet which gets populated by information but have left out the top row. I want to use the tope row to enter my header fields but was looking to see if there a more effcicent way of doing this.

I am using the following code below but was wondering if there was a way using arrays as variants or ranges to do it more efficiently.

With wsNew
    wsNew.Activate
    .Range("A1").Value = "Compt. Code"
    .Range("A2").Value = "Test 2"
    .Range("A3").Value = "Test 3"
    .Range("A4").Value = "Test 4"
    .Cells.Select
    Selection.Columns.AutoFit
    Selection.Rows.AutoFit
End With

cheers for your help.

Jag

A few approaches, one might be:

Dim vHeaders As Variant
vHeaders = Array("Compt. Code", "Test 2", "Test 3", "Test 4")
With wsNew
    With .Range("A1").Resize(UBound(vHeaders)+1)
        .Value = Application.Transpose(vHeaders)
        .Columns.AutoFit
        .Rows.AutoFit
    End With
End With

EDIT: I revised the above to use a resize thereby making the code more flexible, ie header range will expand contract based on number of items in Array.

Hello.

To me that looks pritty good. VBA does not require selection very often

With wsNew
    .Range("A1").Value = "Compt. Code"
    .Range("A2").Value = "Test 2"
    .Range("A3").Value = "Test 3"
    .Range("A4").Value = "Test 4"
    .Columns.AutoFit
    .Rows.AutoFit
End With

I thought the headers were supposed to be in the top row, not first column?

Dim vHeaders As Variant
vHeaders = Array("Compt. Code", "Test 2", "Test 3", "Test 4")
With wsNew
    With .Range("A1").Resize(, UBound(vHeaders)+1)
        .Value = vHeaders
        .Columns.AutoFit
        .Rows.AutoFit
    End With
End With