VBA & Macro Problems

Create Dynamic Excel Columns and Formulas with VBA

Elise WilliamsElise Williams Nov 18, 2026 869 views

Question details

Create a specified number of worksheet columns with headers and formulas based on a number entered in a cell.

Create Dynamic Excel Columns and Formulas with VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the worksheet code

In desktop Excel, right-click the tab of the worksheet containing G3 and select View Code.

2
Add the event macro

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

3
Save and enable the macro

Select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). Reopen the workbook and enable macros if prompted and you trust the file.

4
Test the count

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.

5
Adapt the output

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.

Use a Worksheet Change event to generate columns
Use desktop Excel: Excel for the web cannot run VBA macros. Invalid entries leave the previous output unchanged and display a message.
Free Microsoft Office alternative

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.

Create and edit spreadsheet headers and formulas in WPS SpreadsheetsOpen and save common Microsoft Excel formats, including XLSXUse a familiar interface for everyday spreadsheet tasksMove spreadsheet files between WPS Office and Microsoft Office
microsoft office alternative - wps office

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.