Excel VBA Macro to Copy Budget Data and Hide Empty Rows and Columns
Question details
Copy a completed budget template to a new worksheet and show only rows and columns containing budget data.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A budget template has headings in rows and schedules in columns, but some are unused for a particular budget.
- Observed behavior
- The desired copy retains the budget layout while hiding unused rows and columns.
Identify the budget data range, heading rows, and heading columns, and decide whether zeros, formulas returning an empty string, and errors count as data. Test with a workbook containing dummy data; the example below treats zeros and errors as data but ignores whitespace and formulas returning an empty string.
Copy the Budget Sheet and Hide Empty Data Rows and Columns
Use this example when a rectangular budget data area determines which rows and schedule columns should remain visible.
The macro copies the active worksheet within the same workbook, then checks only the data area. Row and column headings do not count as data; headings outside the data area remain visible. Adjust the range and rules to match your template.
The example unhides rows and columns in the data area before applying its own visibility rules. It hides rather than deletes unused rows and columns. Test carefully if the template uses merged cells or sheet protection.
Open the budget workbook in desktop Excel and identify the data body. The example uses B5:Z100, with column headings above row 5 and row headings to the left of column B. Change the range in the code to fit your sheet.
Press Alt+F11 to open the VBA editor, choose Insert > Module, and paste the following code: Option Explicit Sub CopyBudgetAndHideEmpty() Dim sourceSheet As Worksheet Dim copiedSheet As Worksheet Dim dataArea As Range Dim rowRange As Range, colRange As Range, cell As Range Dim hasData As Boolean Set sourceSheet = ActiveSheet sourceSheet.Copy After:=sourceSheet.Parent.Sheets(sourceSheet.Parent.Sheets.Count) Set copiedSheet = ActiveSheet Set dataArea = copiedSheet.Range("B5:Z100") dataArea.EntireRow.Hidden = False dataArea.EntireColumn.Hidden = False For Each rowRange In dataArea.Rows hasData = False For Each cell In rowRange.Cells If HasBudgetValue(cell) Then hasData = True Exit For End If Next cell rowRange.EntireRow.Hidden = Not hasData Next rowRange For Each colRange In dataArea.Columns hasData = False For Each cell In colRange.Cells If HasBudgetValue(cell) Then hasData = True Exit For End If Next cell colRange.EntireColumn.Hidden = Not hasData Next colRange End Sub Private Function HasBudgetValue(ByVal cell As Range) As Boolean Dim value As Variant value = cell.Value2 If IsError(value) Then HasBudgetValue = True ElseIf IsEmpty(value) Then HasBudgetValue = False Else HasBudgetValue = (Len(Trim$(CStr(value))) > 0) End If End Function
Choose File > Save As and save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Select the budget worksheet, then use Developer > Macros, select CopyBudgetAndHideEmpty, and click Run.
Open the new worksheet tab and check that the expected rows and columns are visible. If needed, change B5:Z100 or the HasBudgetValue rules in the VBA editor and run the macro again from the original budget sheet.
On the budget sheet, choose Developer > Insert > Button (Form Control), draw the button, and assign CopyBudgetAndHideEmpty. Click the button from that sheet to create a new copy.

Review and Edit Budget Workbooks in WPS Spreadsheet
WPS Office is a free, lightweight alternative with a familiar interface for opening and editing budget workbooks. Its Office format compatibility makes it easy to move existing files into WPS; it does not fix or guarantee compatibility with this Excel VBA macro.

Frequently Asked Questions
Does the macro delete empty rows and columns?
No. It hides them on the copied worksheet. To show them again, select the surrounding rows or columns and use Home > Format > Hide & Unhide.
Will a formula that returns an empty string keep a row visible?
Not in this example. The macro checks the displayed result through Value2, so a formula returning an empty string is treated as blank. Numeric zeros and errors count as data.
Where is the copied budget saved?
The macro creates a new worksheet at the end of the same workbook. Save the workbook to keep the copied sheet.
Why is a heading hidden even though it contains text?
The example checks only B5:Z100, not the headings outside that range. If a data row or column is empty, its entire row or column is hidden along with its heading. Change the macro if headings should count as data.
What should I provide if the example does not fit my budget template?
Prepare a workbook with dummy data, an example of the expected output, and details of which headings and cells must remain visible. Include the desired button behavior, but do not share confidential production data.




