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?
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 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
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.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!