Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to auto hide rows if blank cells in a column?

If you have a range of data which populate some blank cells in a column, and now, you want to hide the rows which contain the blank cells in that column automatically. Are there any good ways to solve this job in Excel quickly and easily?

Auto hide rows if blank cells in a column with VBA code

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial


arrow blue right bubble Auto hide rows if blank cells in a column with VBA code

The following code may help you to hide all rows if blank cells in a specific column at once, and if you delete the cell content in that column, the rows will be hide automatically, too. Please do as follows:

1. Right click at the sheet tab that you want to auto hide the rows if there are blank cells in a column , and then choose View Code from the context menu, in the popped out Microsoft Visual Basic for Applications window, please copy and paste the following code into the blank Module:

VBA code: Auto hide rows if blank cells in a column:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160913
    Dim xRg As Range
    Application.ScreenUpdating = False
        For Each xRg In Range("A1:A20")
            If xRg.Value = "" Then
                xRg.EntireRow.Hidden = True
        
            Else
                xRg.EntireRow.Hidden = False
            End If
        Next xRg
    Application.ScreenUpdating = True
End Sub

doc auto hide rows 1

Note: In the above code, A1:A20 is the data list which contains the blank cells you want to auto hide.

2. Then go back to the worksheet, and now, when you double click any cell and press Enter key, the rows which contain blank cells in column A have been hide at once, and if you clear any cell content in the specified cells of column A, the rows will be hide automatically.

doc auto hide rows 2


Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for you.

  • Designed for 1500+ work scenarios, helps you solve 80% Excel problems.
  • Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  • Being used by 110,000 elites and 300+ well-known companies.

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Josh · 2 months ago
    The formula works to hide cells based on information, at the beginning, but does not re-reveal cells if their content changes. I want it to reveal any new cells that have been filled with information (which occurs via a cell lookup). How do I do this?
  • To post as a guest, your comment is unpublished.
    ACHINTA PANDE · 3 months ago
    I want to know whether the code will work If the cell is blank as a result of Formula ?
    • To post as a guest, your comment is unpublished.
      skyyang · 3 months ago
      Hello, ACHINTA,
      The above VBA code is also applied to the blank cells as a result of formula, you could try it, thank you!
  • To post as a guest, your comment is unpublished.
    فتحى السعيد · 8 months ago
    بسم الله وجزاكم الله خيرا هذا الكود رائع ولكن حين قمت بتطبيق كود حذف الصف يبطأ الشيت بطريقة طويلة جدا فما العمل؟
  • To post as a guest, your comment is unpublished.
    chinmay salpe · 11 months ago
    how to Auto hide rows if blank cells in 2 different columns with VBA code
    • To post as a guest, your comment is unpublished.
      skyyang · 11 months ago
      Hi, salpe,
      To hide the rows if blank cells in 2 different columns automatically, please apply the following code:

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim xRg, xCell As Range
      Dim xRgs, xRgArea As Range
      Set xRgs = Range("A1:A22,D1:D22")
      Application.ScreenUpdating = False
      On Error GoTo Ctn
      For Each xRgArea In xRgs.Areas
      Debug.Print xRgArea.Address
      For Each xRg In xRgArea.Columns
      For Each xCell In xRg.Rows
      If xCell.Value = "" Then
      xCell.EntireRow.Hidden = True
      GoTo Ctn
      ElseIf Not xCell.EntireRow.Hidden Then
      xCell.EntireRow.Hidden = False
      End If
      Ctn:
      Next
      Next
      Next
      Application.ScreenUpdating = True
      End Sub

      You can change the cell references to your need.
      Please try it!
  • To post as a guest, your comment is unpublished.
    alex · 1 years ago
    Can I stop this macro from running automatically after hitting enter? Could I attach it to a button to only hide the cells when I click?
    • To post as a guest, your comment is unpublished.
      devin · 10 months ago
      did anyone ever figure this out? I too would like to know.
      • To post as a guest, your comment is unpublished.
        Koop · 9 months ago
        anyone come up with the answer yet?
        • To post as a guest, your comment is unpublished.
          andrea · 6 months ago
          same question here
          • To post as a guest, your comment is unpublished.
            skyyang · 6 months ago
            Hello, guys,
            To hide the rows which a specific column contains blank cells by using a button, please do as this:
            First, you should insert a Command Button from the Developer tab.
            And then apply the following VBA code for the Command Button, (Note: please change the cell reference a1:a20 to your own)

            Private Sub CommandButton1_Click()
            Dim rng As Range, x As Range
            Set rng = Range("a1:a20")
            Application.ScreenUpdating = False
            For Each x In rng
            If Len(x.Text) = 0 Then
            x.EntireRow.Hidden = True
            Else
            x.EntireRow.Hidden = False
            End If
            Next x
            Application.ScreenUpdating = True
            End Sub

            Please try it, hope it can help you!