跳到主要内容

如果列中有空白单元格,如何自动隐藏行?

现在,如果您拥有一系列填充一列中某些空白单元格的数据,那么您想自动隐藏包含该列中空白单元格的行。 有什么好方法可以快速,轻松地在Excel中解决此工作?

如果具有VBA代码的列中的空白单元格,则自动隐藏行


箭头蓝色右气泡 如果具有VBA代码的列中的空白单元格,则自动隐藏行

如果特定列中的空白单元格一次出现,下面的代码可以帮助您隐藏所有行;如果删除该列中的单元格内容,行也将自动隐藏。 请执行以下操作:

1。 如果某列中有空白单元格,请在要自动隐藏行的工作表选项卡上单击鼠标右键,然后选择 查看代码 从上下文菜单中,弹出 Microsoft Visual Basic应用程序 窗口,请复制以下代码并将其粘贴到空白处 模块:

VBA代码:如果列中的空白单元格,则自动隐藏行:

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

doc自动隐藏第1行

备注:在上面的代码中, A1:A20 是数据列表,其中包含您要自动隐藏的空白单元格。

2。 然后返回工作表,现在,当您双击任何单元格并按 输入 键,包含在A列中空白单元格的行已被立即隐藏,如果您清除了A列的指定单元格中的任何单元格内容,则这些行将自动隐藏。

doc自动隐藏第2行

 

最佳办公生产力工具

🤖 Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

产品描述


Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!

 

Comments (35)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I already copied the code, but nothing happened in my worksheet, Do I need to change anything with the code?
Thank you.
This comment was minimized by the moderator on the site
I already copied the code, but nothing happened in my worksheet, Do I need to change anything with the code?
Thank you.
This comment was minimized by the moderator on the site
I'm trying to find a way hide blank rows in sheet2 from A2:A20, if Sheet!A5=Yes. any suggestion?
This comment was minimized by the moderator on the site
Hi, i'm having issues with the "automation" of the macro. I'm searching for a way for the code to work as an automated process without the needs of "manually" running the macro. i've been using my own variant of your code due to me finding issues to use your code. This is my variant:

Sub HideRows()
Dim xRg As Range
Application.ScreenUpdating = False
For Each xRg In Range("A3:A800")
If (xRg.Value = "") Then
xRg.EntireRow.Hidden = True
Else
xRg.EntireRow.Hidden = False
End If
Next xRg
Application.ScreenUpdating = True
End Sub

I'm trying to create a spreadsheet which requires everchanging values, therefore would require a genuine "automated" procress. I'm relatively new to VBA and if your code actually already meets my requirements would you mind helping/teach me to apply it in VBA? Thanks.
This comment was minimized by the moderator on the site
Hello, Vian
In fact, the code in our article can be run automatically.
You must copy and paste the code into the code module of current worksheet, then return to the worksheet, double-click any cell, and press the Enter key, the row containing the blank cell will be is directly hidden.

Please follow the method in this article step by step, hope it can help you!
Thank you!
This comment was minimized by the moderator on the site
apakah ada rumus lain?, saya berharap baris kosong itu akan terhapus saat mencetak file menjadi PDF tapi tampilan di excelnya tetap
This comment was minimized by the moderator on the site
Hello, Nurjanah
To solve your problem, first, you should hide the blank rows, and then print the data, after printing the data, you need unhide the blank rows again. Please do as this:
1. Apply this formula: =COUNTA(A2:E2) beside your data, see screenshot:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-hide-blanks-1.png
2. Then, filter the new helper Blank column, hide all 0 value rows, see screenshot:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-hide-blanks-2.png
3. And then, you should hide the new Blank column, and print the data, after printing the worksheet, please cancel the filter to unhide the blank rows as you need.
Please try, hope it can help you!
This comment was minimized by the moderator on the site
When executing the code in office 2013/2019/2021 it works but takes too much time to complete (only 95 rows to hide).
How can you speed this?
Thanks!
This comment was minimized by the moderator on the site
Hi, Asaf,
The code works well in my Excel file, could you upload your attachment worbook here if you don't mind? So that we can help to check the problem.

Thank you!
This comment was minimized by the moderator on the site
How to hide row which contain checkbox?
This comment was minimized by the moderator on the site
Hello, Sweta

To hide rows with checkbox, the below article may do you a favor:
How To Hide Checkbox When Row Is Hidden In Excel?

Please try, if you still have any other question, please comment here.
This comment was minimized by the moderator on the site
If the value in column E is blank or 0 i would like the row to auto hide. If the value in E changes to something other than blank or 0 I would like it to show. Report is 1500 rows
This comment was minimized by the moderator on the site
Hello, Cathy,
To hide the rows automatically based on blank cells or 0 values, please use the below vba code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    Dim xRg As Range
    Application.ScreenUpdating = False
        For Each xRg In Range("E1:E1500")
            If (xRg.Value = "") Or (xRg.Value = "0") Then
                xRg.EntireRow.Hidden = True
            Else
                xRg.EntireRow.Hidden = False
            End If
        Next xRg
    Application.ScreenUpdating = True
End Sub


Please try, hope it can help you!
This comment was minimized by the moderator on the site
I would like to auto hide rows if column E is blank or 0
This comment was minimized by the moderator on the site
This is working great for me, It hides the rows and opens them when my values change based on formulas.  The question I have is my sheet is 104 rows long.  It churns for a bit each time I click a cell.  Any way to speed up the transition when it is activated?  Or is it my computer?Thanks!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations