VBA & Macro Problems

Excel VBA: Hide Blank Rows and Save Each Sheet Using K11

Adam DavisAdam Davis Nov 18, 2026 870 views

Question details

Create an Excel VBA macro that hides blank rows on every worksheet and saves each sheet separately using the value in K11 as its file name.

Hide Blank Rows and Save Each Excel Sheet Using K11
Product
Microsoft Excel
Device & OS
not provided
Scenario
A workbook contains multiple worksheets that need to be exported as separate files after blank rows are hidden.
Observed behavior
Each worksheet should retain its hidden rows in a separate workbook, with K11 providing the suggested file name.
Before you start

Enable the Developer tab in Excel and confirm which column identifies blank rows and where the data starts. The example checks column B from row 15 onward and assumes row 14 is a header.

Solution 1Recommended

Run a VBA macro to hide blank rows and save each worksheet

Use this macro when a blank cell in column B identifies a row to hide and you want to choose where to save each worksheet.

The macro checks all worksheets in the active workbook. It hides rows whose column B cell is empty or contains only spaces, then copies each sheet to a new workbook. Change the column and starting row in the code if your layout differs.

The Save As dialog suggests the value in K11 as the file name. The exported .xlsx files do not retain VBA code.

1
Open the VBA editor

Open the workbook in desktop Excel, select Developer > Visual Basic, and choose Insert > Module.

2
Paste the macro

Paste this code into the module: Sub HideAndSaveSheets() Dim src As Workbook, ws As Worksheet, exported As Workbook Dim lastCell As Range, r As Long, nameText As String Dim savePath As Variant, ch As Variant On Error GoTo HandleError Set src = ActiveWorkbook Application.ScreenUpdating = False For Each ws In src.Worksheets Set lastCell = ws.Cells.Find(What:="*", LookIn:=xlFormulas, _ SearchOrder:=xlByRows, SearchDirection:=xlPrevious) If Not lastCell Is Nothing Then For r = 15 To lastCell.Row If Not IsError(ws.Cells(r, "B").Value) Then If Len(Trim(CStr(ws.Cells(r, "B").Value))) = 0 Then ws.Rows(r).Hidden = True End If End If Next r End If If IsError(ws.Range("K11").Value) Then nameText = ws.Name Else nameText = Trim(CStr(ws.Range("K11").Value)) End If For Each ch In Array("\", "/", ":", "*", "?", Chr(34), "<", ">", "|") nameText = Replace(nameText, ch, "_") Next ch Do While Len(nameText) > 0 If Right(nameText, 1) <> "." And Right(nameText, 1) <> " " Then Exit Do nameText = Left(nameText, Len(nameText) - 1) Loop If Len(nameText) = 0 Then nameText = ws.Name ws.Copy Set exported = ActiveWorkbook savePath = Application.GetSaveAsFilename( _ InitialFileName:=nameText & ".xlsx", _ FileFilter:="Excel Workbook (*.xlsx), *.xlsx") If VarType(savePath) <> vbBoolean Then exported.SaveAs Filename:=CStr(savePath), FileFormat:=xlOpenXMLWorkbook End If exported.Close SaveChanges:=False Set exported = Nothing Next ws CleanUp: Application.ScreenUpdating = True Exit Sub HandleError: MsgBox "The export stopped: " & Err.Description, vbExclamation If Not exported Is Nothing Then exported.Close SaveChanges:=False Resume CleanUp End Sub

3
Run and save each sheet

Return to Excel, activate the workbook you want to process, and select Developer > Macros. Choose HideAndSaveSheets and select Run. Confirm or change each suggested file name in the Save As dialog; select Cancel to skip that sheet.

Run a VBA macro to hide blank rows and save each worksheet
The source worksheets also change: The macro hides rows in the active source workbook before copying each sheet. It does not unhide rows that were already hidden, and an error stops the remaining exports.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and continue working with rows, filters, and worksheets as you migrate your everyday spreadsheet work.

Hide rows and use filters to organize worksheet dataOpen and edit Microsoft Excel .xlsx and .xls filesUse a familiar spreadsheet interfaceWork with a lightweight office suite for free
microsoft office alternative - wps office

Frequently Asked Questions

How do I check a different column for blank rows?

In the VBA editor, change "B" in ws.Cells(r, "B") to the column you want to check. Change 15 in the For loop if your first data row is different.

Does the macro hide only completely empty rows?

No. It hides a row when its column B cell is blank or contains only spaces, even if other cells in that row contain data. Cells containing errors are not treated as blank.

What happens if K11 is empty or contains an error?

The macro uses the worksheet name as the suggested file name. You can change the name in the Save As dialog.

Will the exported files contain the macro?

No. The macro saves each copied sheet as an .xlsx workbook, which cannot retain VBA code. Keep the macro in a macro-enabled workbook if you need to run it again.

How can I hide rows containing a hyphen instead of blank cells?

In the VBA editor, replace the blank-cell condition with a comparison such as Trim(CStr(ws.Cells(r, "B").Value)) = "-". Keep the existing IsError check around that comparison.