Create Dynamic Excel Columns and Formulas with VBA
Question details
Create a specified number of worksheet columns with headers and formulas based on a number entered in a cell.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user enters a column count in a worksheet cell and wants VBA to update a designated output area.
- Observed behavior
- The output area should contain the requested number of headers and formulas, with extra generated content removed when the count decreases.
Decide where the generated columns, headers, and formulas should go. The example uses G3 for the count and clears H5:AG6, so make sure that output area contains no data you need to keep.
Use a Worksheet Change event to generate columns
Use this example when entering a whole number in G3 should update headers and formulas automatically.
This example creates up to 26 columns in H:AG. It writes headers in row 5 and formulas in row 6; each formula doubles the value in row 4 of the same column. It clears cell contents in the output area rather than inserting or deleting entire worksheet columns.
A direct edit to G3 triggers the event. A change to a formula result in G3 does not trigger Worksheet_Change.
In desktop Excel, right-click the tab of the worksheet containing G3 and select View Code.
Paste the following code into that worksheet's code window. If the worksheet already has a Worksheet_Change procedure, combine the logic with the existing procedure instead of adding a second one. Private Sub Worksheet_Change(ByVal Target As Range) Dim valueInCell As Variant Dim countValue As Double Dim i As Long If Intersect(Target, Me.Range("G3")) Is Nothing Then Exit Sub valueInCell = Me.Range("G3").Value If IsError(valueInCell) Then MsgBox "Enter a whole number from 0 to 26 in G3." Exit Sub End If If Len(Trim(CStr(valueInCell))) = 0 Then countValue = 0 ElseIf Not IsNumeric(valueInCell) Then MsgBox "Enter a whole number from 0 to 26 in G3." Exit Sub Else countValue = CDbl(valueInCell) End If If countValue < 0 Or countValue > 26 Or countValue <> Int(countValue) Then MsgBox "Enter a whole number from 0 to 26 in G3." Exit Sub End If Me.Range("H5:AG6").ClearContents For i = 1 To CLng(countValue) Me.Cells(5, 7 + i).Value = "Column " & i Me.Cells(6, 7 + i).Formula = "=" & Me.Cells(4, 7 + i).Address(False, False) & "*2" Next i End Sub
Select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). Reopen the workbook and enable macros if prompted and you trust the file.
Enter 3 in G3. Check that H5:J5 contain headers and H6:J6 contain formulas. Enter a smaller number to remove extra generated contents, or clear G3 to remove all of them.
In View Code, change the header text, row numbers, formula pattern, or output range to match your worksheet. Keep the clearing range, starting column, and maximum count consistent.

Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface and support for common Excel formats, making it easier to move everyday spreadsheet work between applications.

Frequently Asked Questions
Where should I paste the Worksheet_Change code?
In desktop Excel, right-click the tab of the worksheet containing G3, select View Code, and paste the procedure into that worksheet's code window.
How do I change the headers or formulas?
Open the worksheet's code with View Code. Edit the text after "Column " for the headers and the Formula assignment for the formula pattern. Update the row numbers and clearing range if you move the output.
Why doesn't the macro run when a formula changes G3?
Worksheet_Change responds to edits to cells, not to recalculation of a formula result. If G3 contains a formula, a different approach, such as a Worksheet_Calculate event, is needed.
Will the macro delete entire columns?
No. The example clears contents in H5:AG6 and writes new headers and formulas there. It does not insert or delete worksheet columns.
What file format preserves the VBA code?
In Excel, use File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). An XLSX file cannot preserve VBA code.




