Outputting data without using a loop

If you are creating a table in a worksheet, you can use a template sheet with pre-made column names, or sometimes the column names are generated dynamically.

If you want to print the column names in VBA code, you can either print the column names for each cell individually, or you can smartly put them in an array and loop through them if there is too much code.

The following examples show how to output multiple values in a range of cells without using a loop, including outputting a single value, using a VBA array, using an Excel array literal, mixing two arrays, and using worksheet functions TODAY() and NOW().

Sub demoResize()
  Dim myStr As String

  myStr = "a string from vba"
  With Sheet1.Range("A1")
    .Offset(0, 1).Resize(1, 2) = "A value"
    .Offset(1, 1).Resize(1, 2) = Array("Price", "Delta")
    .Offset(2, 1).Resize(3, 2) = [{"Zip", "22150";"City", "Springfield"; "State", "VA"}]
    .Offset(5, 1).Resize(1, 3) = Array([Today()], [Now()], myStr)
  End With
End Sub

If you are working on a repetitive and complex M&A valuation, you can use the Rezise function as follows. The code below is very simple to demonstrate the use of the function, but if you are working on a real-world scenario, you will want to create more complex code.

Sub demoResize2()

    With Sheet1.Range("A10")
        .Offset(0, 1) = "RETURN ON EQUITY (ROE)"
        .Offset(1, 1).Resize(1, 7) = Array("Year", 0, 1, 2, 3, 4, 5)
        .Offset(2, 1).Resize(1, 7) = Array("Profit after tax (net profit)", 0, 190, 266, 354, 457, 578)
        .Offset(3, 1).Resize(1, 7) = Array("Equity", 1100, 1290, 1556, 1910, 2367, 2945)
        .Offset(4, 1).Resize(1, 7) = Array("Return on equity", 0, 16, 19, 20, 21, 22)
    End With
End Sub