Learn how to delete all hidden rows in Excel in this comprehensive and beginner-friendly guide.

Imagine your Excel workbook is like a crowded closet. Everything you need is in there, but hidden clutter makes it hard to find what’s useful.

Hidden rows can have the same impact on your data—extra information that gets in the way and makes managing your spreadsheet a chore. Also, these hidden rows unnecessarily increase the workbook size, especially for a large dataset.

Deleting all hidden rows in Excel may seem complicated, but it’s easier than you think. In this Microsoft Excel tutorial, I’ll present simple methods and their steps to help you quickly clear those hidden rows.

Ready to tidy up your spreadsheet and keep only what you need? Let’s dive in and start simplifying!

Using the Inspect Workbook Tool

If you’re using any of the modern Excel desktop apps, like Excel 2016 or newer, you can simply use the Inspect Workbook tool to look up hidden rows and columns. Once identified, the tool also offers an option to delete those.

Copy the source Excel file to a different drive. It’ll help retrieve data from this backup file if anything goes wrong.

Open the workbook from which you want to remove all hidden columns and rows. Ensure there are truly some hidden cells. Otherwise, the tool won’t fetch any data on hidden rows and columns.

Now, click on the File tab and select Info in the left-side navigation panel on the Excel backstage view.

You should now see the Inspect Workbook section on the right side.

Click on the Check for Issues drop-down arrow and choose Inspect Document in the context menu.

A warning dialog will pop up. Click Yes to proceed.

Hit the Inspect button on the Document Inspector dialog box.

Once the scanning is over, scroll down to the bottom of the Document Inspector dialog to find the Hidden Rows and Columns analytics.

Click on the Remove All button.

You can now click Close to go back to your workbook.

Here are the downsides of this approach to removing hidden rows in Excel:

• It’s only available in the newer Excel editions.
• You can’t use the Excel undo feature to roll back the changes.
• This tool automatically scans for and selects all the hidden rows and columns.
• When you delete hidden rows and columns, the action is applied to the whole workbook.
• If you’re using Excel for Mac or Excel for the web app, Inspect Workbook won’t be available on those platforms.

Using the Go To Dialog Box

When you can’t use the Inspect Workbook tool, for example on a Macbook, you can manually copy and paste the used cells to a different worksheet and delete the source sheet. This is also a workaround for situations when you only want to remove hidden rows from specific sheets.

When on the source worksheet, press Ctrl + G to bring up the Go To dialog.

Click Special to access the Go To Special menu.

Select Visible cells only and click OK.

Excel will select all the rows and columns except the hidden ones.

Press Ctrl + C to copy all visible cells.

Click on the New sheet button in the sheet tab to create a blank worksheet.

Go to the new sheet and press Ctrl + V to paste the copied cells.

You can now right-click on the source worksheet’s tab and select Delete.

Using Excel VBA

If you frequently need to remove hidden rows from a specific worksheet in Windows or Mac, the best option is Excel VBA-based automation.

Even if you’ve never used VBA programming in Excel, you can still try this method by following the simple steps outlined in this tutorial.

My VBA macro will walk you through the whole process of selecting the source worksheet, identifying hidden rows that are used in a formula, and automatically deleting unwanted hidden rows visually with descriptive instructions.

Before proceeding further, make a backup copy of the source workbook and place it in a different directory or drive. You can use it later if you need to recover old data from hidden rows.

Firstly, if you’re new to creating and running an Excel VBA macro, go through this quick tutorial to learn the process:

📒 Read More: How To Use The VBA Code You Find Online

Now, use the following VBA script to set up a VBA macro:

 Sub RemoveHiddenRows()
    Dim ws As Worksheet
    Dim wsIndex As Integer
    Dim wsNames As String
    Dim inputSheets As String
    Dim wsArray As Variant
    Dim wsChoice As Variant
    Dim cell As Range
    Dim msg As String
    Dim dep As Range
    Dim foundDependency As Boolean

    wsIndex = 1
    For Each ws In ThisWorkbook.Worksheets
        wsNames = wsNames & wsIndex & ". " & ws.Name & vbCrLf
        wsIndex = wsIndex + 1
    Next ws

    inputSheets = InputBox("Enter the numbers separated by commas for the sheets to remove hidden rows:" & vbCrLf & wsNames, "Remove Hidden Rows")
    wsArray = Split(inputSheets, ",")

    For Each wsChoice In wsArray
        wsChoice = Trim(wsChoice)
        If IsNumeric(wsChoice) Then
            wsIndex = CInt(wsChoice)
            If wsIndex > 0 And wsIndex <= ThisWorkbook.Worksheets.Count Then
                Set ws = ThisWorkbook.Worksheets(wsIndex)

                foundDependency = False
                For Each cell In ws.UsedRange
                    If cell.EntireRow.Hidden Then
                        On Error Resume Next
                        Set dep = cell.DirectDependents
                        On Error GoTo 0
                        If Not dep Is Nothing Then
                            For Each dCell In dep
                                If Not dCell.EntireRow.Hidden Then
                                    foundDependency = True
                                    msg = msg & "Sheet: " & ws.Name & ", Row: " & cell.Row & vbCrLf
                                    cell.EntireRow.Hidden = False
                                    cell.EntireRow.Interior.Color = RGB(255, 255, 0) ' Highlighting with yellow
                                    Exit For
                                End If
                            Next dCell
                        End If
                    End If
                Next cell

                If foundDependency Then
                    If MsgBox("The following hidden rows have dependencies on visible cells:" & vbCrLf & msg & "Do you want to proceed?", vbYesNo + vbExclamation, "Warning") = vbNo Then
                        Exit Sub
                    End If
                End If

                For Each cell In ws.UsedRange
                    If cell.EntireRow.Hidden Then
                        cell.EntireRow.Delete
                    End If
                Next cell
            End If
        End If
    Next wsChoice

    MsgBox "Task completed successfully!", vbInformation

End Sub

Once the macro is ready, press the Alt + F8 keys to launch the Macro selection box.

Select the RemoveHiddenRows macro and click on the Run button.

You’ll see a dialog box asking you to enter the ID numbers of the worksheets from which you want to delete all hidden rows. For example, 1 for Sheet1 in this tutorial.

Next, the macro will scan through the worksheets for dependencies. if there are none, Excel will remove the hidden rows.

However, if the macro detects any dependencies in the hidden rows, it’ll show a warning dialog along with the row numbers.

If you choose Yes, the script will delete all hidden rows.

Contrarily, if you select No, Excel will abort the program, and unhide the hidden rows that are dependencies for some formulas in the visible rows. These rows will be automatically highlighted in yellow.

Using Office Scripts

Suppose, you’d like to create cloud-based automation workflows that trigger Excel spreadsheet actions based on Microsoft 365 services through Power Automate, you need to use Office Scripts. Luckily, there’s a dedicated Office Script available to remove hidden rows.

If you see the Automate tab in your Excel desktop or web app, you can proceed with the quick steps outlined below.

Click on the New Script command in the Scripting Tools block of the Automate tab.

The Code Editor will open on the right-side navigation panel. Select everything inside the Code Editor and press Delete.

Now that the Office Scripts editing interface is empty, copy and paste the following script inside the Code Editor:

function main(workbook: ExcelScript.Workbook) {
    // Get the active worksheet
    let sheet: ExcelScript.Worksheet = workbook.getActiveWorksheet();

    // Get the used range of the worksheet
    let usedRange: ExcelScript.Range = sheet.getUsedRange();

    // Get the number of rows in the used range
    let rowCount: number = usedRange.getRowCount();

    // Store hidden rows to delete
    let hiddenRows: number[] = [];

    // Loop through each row to check if it is hidden
    for (let i = 0; i < rowCount; i++) {
        let row: ExcelScript.Range = usedRange.getCell(i, 0).getEntireRow();
        if (row.getHidden()) {
            hiddenRows.push(i);  // Store 0-based row index
        }
    }

    // Delete hidden rows in reverse order to avoid shifting issues
    hiddenRows.reverse().forEach(rowIndex => {
        usedRange.getCell(rowIndex, 0).getEntireRow().delete(ExcelScript.DeleteShiftDirection.up);
    });
}

Click Save script and give the code a name so you can use it in the future or share it with a colleague or friend.

Now, navigate to the source worksheet, and hit the Run button.

Office Scripts will loop through the active worksheet, locate all hidden rows, remove, and update the spreadsheet with visible cells.

📝 Note: This method applies to you if you’re using Excel for the Microsoft 365 desktop app on a compatible Windows PC or Macbook. The feature is also available on Excel for the web app. Also, you must own Microsoft 365 Business Standard or a better subscription plan.

Conclusions

Deleting all hidden rows in an Excel workbook is easy when you can use the Inspect Workbook tool.

However, if you can’t use this method, you can use the manual workaround using the Go To dialog box.

Finally, if you’re looking to automate the process at the desktop level, use Excel VBA, and for Microsoft 365 cloud services level, you can try the Office Scripts-based automation.

Comment below to let us know which one you’d prefer. Also, if you know of a better technique I might’ve missed, mention that in your reply.