Excel VBA: Hide Blank Rows and Save Each Sheet Using K11
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.

- 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.
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.
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.
Open the workbook in desktop Excel, select Developer > Visual Basic, and choose Insert > Module.
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
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.

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.

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.




