跳到主要内容

如果单元格在Excel中包含零,如何删除整行?

作者:孙 最后修改时间:2020-05-21

有时,如果在Excel中该单元格包含零,则希望删除整行,如果其中包含几个单元格,则可以将它们一一删除。 但是,需要删除几百行包含零的行吗? 您可以选择以下一种棘手的方法来解决它。

如果单元格中包含零与Excel中的筛选器功能删除行
如果Excel中的VBA单元格包含零,则删除行
如果单元格包含零,则使用 Kutools for Excel 删除行


如果单元格中包含零与Excel中的筛选器功能删除行

您可以使用“过滤器”功能基于某一列中的零值过滤掉所有行,然后在以后删除所有可见行。 请执行以下操作。

1.选择包含要删除其整个行的零值的列单元格,然后单击 时间 > 筛选器。 看截图:

2.然后,在所选列的第一个单元格中显示一个下拉箭头,单击箭头,然后选择 号码过滤器 > 等于 从下拉列表中选择。

doc如果为零则删除1

3.在 自定义自动筛选 对话框中,输入数字 0 进入文本框,如下图所示,然后单击 OK 按钮。

doc如果为零则删除1

4.然后,该特定列中的所有零值单元格将被过滤掉。 请选择过滤器范围内的所有可见单元格,然后右键单击它们,选择 删除行 从右键单击菜单中。 然后在弹出的提示框中,点击 OK 按钮。 看截图:

doc如果为零则删除1

5.现在,所有可见行均被删除。 您可以点击 时间 > 筛选器 再次显示所有没有零值单元格的数据。 看截图:

doc如果为零则删除1


如果Excel中的某个范围内存在零值,则轻松删除所有行:

Kutools for Excel的 选择特定的单元格 实用程序可以帮助您轻松地选择整个行(如果在某个范围内存在零值),然后您可以手动快速快速删除所有选定行,而不会出现任何错误。
立即下载并尝试! (30 天免费试用)


如果Excel中的VBA单元格包含零,则删除行

本节将向您展示VBA方法,以删除Excel中某一列中存在零值的所有行。

1。 按 Alt + F11键 同时显示 Microsoft Visual Basic应用程序 窗口。

2.在“ Microsoft Visual Basic for Applications”窗口中,单击“ 插页 > 模块,然后将以下VBA代码复制并粘贴到模块窗口中。

VBA:如果工作表中的某些列范围内存在零值,则删除整个行

Sub DeleteZeroRow()'Updateby20140616 Dim Rng作为范围Dim WorkRng作为错误发生时的范围恢复下一个xTitleId =“ KutoolsforExcel” Set WorkRng = Application.Selection Set WorkRng = Application.InputBox(“ Range”,xTitleId,WorkRng.Address,类型:= 8 )Application.ScreenUpdating = False设置Rng = WorkRng.Find(“ 0”,LookIn:= xlValues)如果不是Rng则为空,则Rng.EntireRow.Delete结束如果循环,而没有Rng则为空

3。 按 F5 运行代码的键,在弹出的 KutoolsForexcel. 对话框中,根据其中的零值选择要删除整个行的列范围,然后单击 OK 按钮。 看截图:

doc如果为零则删除1

然后,将立即删除基于指定列范围内零值的所有行。

doc如果为零则删除1


如果单元格包含零,则使用 Kutools for Excel 删除行

对于许多Excel用户,使用VBA代码在Excel中删除数据很危险。 如果您不完全信任VBA代码,我们建议您在此处尝试 选择特定的单元格 实用程序 Kutools for Excel.

申请前 Kutools for Excel首先下载并安装.

1.选择列范围,您将基于其中的零值删除整个行,然后单击 库工具 > 选择 > 选择特定的单元格。 看截图:

2.在 选择特定的单元格 对话框,您需要:

(1)选择 整行 在选项 选择类型 部分。

(2)选择 等于 在第一个 特定类型 下拉列表,然后输入数字 0 进入文本框。

(3)点击 OK 按钮。

3.弹出一个对话框,告诉您已选择了多少行,单击 OK 按钮。 现在,所有具有零值的行都位于指定的列范围内。 请右键单击任何选定的行,然后单击 删除 在右键菜单中。 看截图:

doc如果为零则删除1

现在,指定列中存在的所有值为零的行将立即删除。 看截图:

doc如果为零则删除1

  如果您想免费试用(30天)此实用程序, 请点击下载,然后按照上述步骤进行操作。


如果单元格包含零,则使用 Kutools for Excel 删除行


相关文章:

最佳办公生产力工具

🤖 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 (15)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Despues de utilizar subtotal Como hacer para eliminar las filas involucradas que en el subtotal es igual a 0?
This comment was minimized by the moderator on the site
Insert this in VB new module.
This is with InputBox to delete row which contains that word.

Attribute VB_Name = "FindDelRowByWord"
Sub FindDelRow()
'Updateby20140616
Dim Rng As Range
Dim WorkRng As Range
Dim xRep As String
On Error Resume Next
xTitleId = "ZOK Tools"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xRep = Application.InputBox("word to delete Row:", "ZOK Tools", , , , , 2)
Application.ScreenUpdating = False
Do
Set Rng = WorkRng.Find(xRep, LookIn:=xlValues)
If Not Rng Is Nothing Then
Rng.EntireRow.Delete
End If
Loop While Not Rng Is Nothing
Application.ScreenUpdating = True
End Sub
This comment was minimized by the moderator on the site
WHAT IF you have a big sheet, and there are rows you want to keep, and others rows that contain certain key words and delete those? the kutools work for me but for one key word that many rows have that key word, the rows were deleted, i just want to have multiple key words to do the same? does this work for the code above?
This comment was minimized by the moderator on the site
Good Day,
Kutools can deal with two key words at the same time. You need to enable its second condition with "And" or "Or". Hope I can help.
This comment was minimized by the moderator on the site
This didn't work for me. It changed all values in my chosen column and the adjacent column to zeros. I must be doing something wrong!
This comment was minimized by the moderator on the site
Hi Sheri,
The code works well in my case. Which Excel verson do you use?
This comment was minimized by the moderator on the site
Hi Crystal. I posted this two years ago so I’m guessing I figured it out. But thanks for the reply.
This comment was minimized by the moderator on the site
Hi dears , I need a code to hide the rows which have the value=0 on the column "N" in the sheet 1. The value of the column "N" will change when update the details on the Sheet 2. that's the time need to un-hide the row. is this possible to do this with Excel formula(without macro and Excel Filters).
This comment was minimized by the moderator on the site
modified for my purpose--thank you for your help: Sub DeleteZeroRow() Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = "" Application.Calculation = xlManual MsgBox "Set the range you want to remove the unused 0 quantity rows from" Set WorkRng = Application.InputBox("Range", xTitleId, "FG93:FG500", Type:=8) Application.ScreenUpdating = False Sheets("ENTRY").Select Do Set Rng = WorkRng.Find("0", LookIn:=xlValues) If Not Rng Is Nothing Then Rng.EntireRow.Delete End If Loop While Not Rng Is Nothing Application.ScreenUpdating = True Range("FF92").Select Selection.End(xlDown).Select MsgBox "Removed 0's ROWS from column FG--calculating now--please wait" Application.Calculation = xlAutomatic End Sub
This comment was minimized by the moderator on the site
I modified the below code to work for me. This deleted every row in column C that had "Delete" in the cell of column C. Sub Delete_DeleteRows() Set WorkRng = Range("C2:C12000") Application.ScreenUpdating = False Do Set Rng = WorkRng.Find("Delete", LookIn:=xlValues) If Not Rng Is Nothing Then Rng.EntireRow.Delete End If Loop While Not Rng Is Nothing Application.ScreenUpdating = True End Sub
This comment was minimized by the moderator on the site
Hi,
its worked for me, thanks a lot.
if I have multiple sheets? how i can run the script 1 time for all the sheets?
This comment was minimized by the moderator on the site
thanks for the response, worked for me
This comment was minimized by the moderator on the site
I modified the above code to work for me. I wanted to delete every row that had "Delete" in row C. Sub Delete_DeleteRows() Set WorkRng = Range("C2:C12000") Application.ScreenUpdating = False Do Set Rng = WorkRng.Find("Delete", LookIn:=xlValues) If Not Rng Is Nothing Then Rng.EntireRow.Delete End If Loop While Not Rng Is Nothing Application.ScreenUpdating = True End Sub
This comment was minimized by the moderator on the site
Your VB code doesn't work. It deletes all rows with a Zero in the 10s position. I hope no one actually uses this as they will delete data...
This comment was minimized by the moderator on the site
This was a life saver!!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations