Range.Table method (Excel)

Creates a data table based on input values and formulas that you define on a worksheet.

Syntax

expression.Table (RowInput, ColumnInput)

expression A variable that represents a Range object.

Parameters

Name Required/Optional Data type Description
RowInput Optional Variant A single cell to use as the row input for your table.
ColumnInput Optional Variant A single cell to use as the column input for your table.

Return value

Variant

Remarks

Use data tables to perform a what-if analysis by changing certain constant values on your worksheet to see how values in other cells are affected.

Example

This example creates a formatted multiplication table in cells A1:K11 on Sheet1.

Set dataTableRange = Worksheets("Sheet1").Range("A1:K11") 
Set rowInputCell = Worksheets("Sheet1").Range("A12") 
Set columnInputCell = Worksheets("Sheet1").Range("A13") 
 
Worksheets("Sheet1").Range("A1").Formula = "=A12*A13" 
For i = 2 To 11 
 Worksheets("Sheet1").Cells(i, 1) = i - 1 
 Worksheets("Sheet1").Cells(1, i) = i - 1 
Next i 
dataTableRange.Table rowInputCell, columnInputCell 
With Worksheets("Sheet1").Range("A1").CurrentRegion 
 .Rows(1).Font.Bold = True 
 .Columns(1).Font.Bold = True 
 .Columns.AutoFit 
End With

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.