跳到主要内容

如何在Excel中根据单元格值删除行?

要基于Excel中的单元格值快速删除或删除多行,您可能需要先选择包含特定单元格值的整行,然后再删除它们。 似乎没有一种快速的方法来删除基于单元格值的行,而是使用VBA代码。 这里有一些快速技巧可以帮助您。


使用查找和替换功能根据单元格值删除行

在Excel中,您可以应用功能强大的“查找和替换”功能轻松地基于某个单元格值删除行。 请执行以下操作:

1。 选择要根据某些单元格值删除行的范围,然后按并打开“查找和替换”对话框 Ctrl + F 键同时。

2。 在“查找和替换”对话框中,请输入特定的单元格值(在本例中,我们输入 e) 进入 Find what 框,然后单击 Find All 按钮。 请参阅下面的第一个屏幕截图:

3。 在“查找和替换”对话框的底部选择所有搜索结果,然后关闭此对话框。 (备注:您可以选择搜索结果之一,然后 Ctrl + A 键以选择所有找到的结果。 请参阅上面的第二个屏幕截图。)
然后您可以看到所有包含特定值的单元格都被选中。

4。 继续右键单击所选单元格,然后选择 Delete 从右键单击菜单中。 然后检查 Entire row 在弹出的删除对话框中选择选项,然后单击 OK 按钮。 现在,您将看到所有包含特定值的单元格都被删除了。 查看以下屏幕截图:

然后已经基于该特定值删除了整行。


使用VBA代码根据单元格值删除行

使用以下VBA代码,您可以快速删除具有某些单元格值的行,请执行以下步骤:

1。 按 Alt + F11 按键同时打开 Microsoft Visual Basic for applications 窗口,

2。 点击 Insert > Module,然后将以下代码输入到模块中:

VBA:根据单元格值删除整个行

Sub DeleteRows()
'Updateby20211217
Dim rng As Range
Dim InputRng As Range
Dim DeleteRng As Range
Dim DeleteStr As String
Dim xTitleId As String
Dim xArr
Dim xF As Integer
Dim xWSh As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, rng.Address, Type:=8)
If InputRng Is Nothing Then Exit Sub
DeleteStr = Application.InputBox("Delete Text", xTitleId, Type:=2)
Set xWSh = InputRng.Worksheet
For Each rng In InputRng
    If rng.Value = DeleteStr Then
        If DeleteRng Is Nothing Then
            Set DeleteRng = rng
        Else
            Set DeleteRng = Application.Union(DeleteRng, rng)
            Set DeleteRng = DeleteRng.EntireRow
        End If
    End If
Next
xArr = Split(DeleteRng.AddressLocal, ",")
DeleteRng.Select
DeleteRng.Delete
For xF = UBound(xArr) To 0 Step -1
    Set DeleteRng = xWSh.Range(xArr(xF))
    DeleteRng.Delete
Next
End Sub

3。 然后点击 Run 按钮运行代码。

4。 在弹出的对话框中,请选择要根据特定值删除行的范围,然后点击 OK 按钮。

5。 在另一个对话框中,请输入您要删除的行所基于的特定值,然后单击 OK 按钮。 看截图:

然后您将看到已经基于指定的值删除了整个行。


使用 Kutools for Excel 根据一个或两个单元格值删除行

如果你已经安装 Kutools for Excel,它的 Select Specific Cells 功能可以帮助您快速删除具有特定值的行。 请执行以下操作:

Kutools for Excel - 使用 300 多种基本工具增强 Excel 功能。 享受全功能 30 天免费试用,无需信用卡! 立即行动吧!

1。 选择要根据特定值删除行的范围,然后单击 Kutools > Select > Select Specific Cells。 看截图:

2。 在打开的“选择指定的单元格”对话框中,请检查 Entire row 选项,选择 Contains Specific type 下拉列表,在右框中输入指定的值,然后单击 Ok 按钮(请参见上面的屏幕截图)。
应用此功能后,将弹出一个对话框,显示根据指定条件找到了多少个单元格。 请点击 OK 按钮关闭它。

3。 现在,选择具有特定值的整个行。 请右键点击选中的行,然后点击 Delete 从右键单击菜单中。 参见下面的截图:

备注: 这个 Select Specific Cells 功能支持删除一个或两个特定值的行。 要基于两个指定值删除行,请在 Specific type “选择特定单元格”对话框的“部分”部分,如以下屏幕截图所示:

Kutools for Excel - 使用 300 多种基本工具增强 Excel 功能。 享受全功能 30 天免费试用,无需信用卡! 立即行动吧!


使用 Kutools for Excel 根据多个单元格值删除行

在某些情况下,您可能需要从Excel中的另一列/列表中删除基于多个单元格值的行。 在这里我将介绍 Kutools for Excel's Select Same & Different Cells 轻松快速解决问题的功能。

Kutools for Excel - 使用 300 多种基本工具增强 Excel 功能。 享受全功能 30 天免费试用,无需信用卡! 立即行动吧!

1。 点击 Kutools > Select > Select Same & Different Cells 打开“选择相同和不同的单元格”对话框。

2。 在开幕 Select Same & Different Cells 对话框,请执行以下操作(请参见屏幕截图):

(1)在 Find values in 框,请选择您将在其中找到某些值的列;
(2)在 According to 框,请选择具有多个值的列/列表,您将基于这些值删除行;
(3)在 Based on 部分,请检查 Each row 选项;
(4)在 Find 部分,请检查 Same Values 选项;
(5)检查 Select entire rows 打开对话框底部的选项。
备注:如果两个指定的列包含相同的标题,请检查 My data has headers 选项。

3。 点击 Ok 按钮以应用此实用程序。 然后出现一个对话框,显示已选择多少行。 只需点击 OK 按钮关闭它。

然后,已选择所有包含指定列表中的值的行。

4。 点击 Home > Delete > Delete Sheet Rows 删除所有选定的行。


演示:根据Excel中的一个或多个单元格值删除行


Kutools for Excel:超过 300 个方便的工具触手可及! 立即开始 30 天免费试用,没有任何功能限制。 立即下载!

最佳办公生产力工具

🤖 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 (39)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
In given range, if any cell contains defined text the entire row gets deleted.

Pls share code
This comment was minimized by the moderator on the site
Hi Yogesh,
On this webpage, the second method is to delete rows by cell values with VBA code. You can go to there and copy the code directly!
https://www.extendoffice.com/documents/excel/815-excel-remove-rows-based-on-cell-value.html#vba
This comment was minimized by the moderator on the site
There are two issues with the code posted as of this date. 1) the variable xTitleId is not declared, which causes a compilation error if Option Explicit is in use. 2) With Excel 2016, it appears that a range is limited to 129 areas. Each discontiguous matching cell found in the loop creates another area in the DeleteRng variable. Unfortunately, the Union method does not raise an error if that number of areas is exceeded; it just ignores the remaining cells. When the loop is finished, DeleteRng will have no more than 129 areas.
This comment was minimized by the moderator on the site
Hi TomTheToolman,
Thank you so much for your feedback. I've already updated the new code in the article. If you have any other questions, please don't hesitate to let me know.
Amanda
This comment was minimized by the moderator on the site
very well. Thank you!
This comment was minimized by the moderator on the site
Can someone help me...If 3rd column has value 0, then delete all values of corresponding column 1st. In this case answer should be the last line only.... Check MenuName ID 3149 VNLA MILFLLE 2 3149 TURKEY PNN 0 3149 R. BEEF PNN 0 3149 MIX MOCHA 38 3150 M.G.R 1/2 0 3150 THE PEPPE L 0 3150 MIX SLD 0 3150 EGGPLANT 0 3150 STILL WATER 7 3151 MIX MOCHA 38
This comment was minimized by the moderator on the site
Thanks for sharing. I am actually looking for a code that doesn't ask user for range but instead selects a specific column say column "A" and runs till the last row of that column. Can you please help..
This comment was minimized by the moderator on the site
Hi everybody, I am wondering what can we do to delete the following (According to the example shown in this page): Soe appears at several date (sept, October... etc). What I would like is to delete the line where Soe is but to keep the line with last date she appeared. In addition, some lines could be in double but I still want to keep it. So for example, you have the lines: - July 3 /Soe - Sep 4 / Soe - Sep 4 / Soe - Oct 19/ Soe - Nov 13 / Soe - Nov 13 / Soe and what I want to keep is: - Nov 13 / Soe - Nov 13 / Soe My real case is: I have different EAN code and version 1, 2, 3 or 4 and I want to keep the line where the version is the higher. e.g.: I have: - EAN 1 / Version 1 - EAN 1 / Version 1 - EAN 1 / Version 2 - EAN 1 / Version 2 - EAN 2 / Version 2 - EAN 2 / Version 3 - EAN 2 / Version 3 and I want to keep: - EAN 1 / Version 2 - EAN 1 / Version 2 - EAN 2 / Version 3 - EAN 2 / Version 3 I am searching since hours and I am completely blocked on this issue. Many thanks in advance for your brain and help. Best, Marion
This comment was minimized by the moderator on the site
Hi, Thank you this was really helpful. However, there's an error that pops up when I run the codes it says "Object variable or with block variable not set" and it points to " the line DeleteRNG.EntireRow.Delete". Could you please help me with debugging this. Thanks.
This comment was minimized by the moderator on the site
I am trying to use this macro in order to delete unused formulas, because excel views blank formula cells as a zero value and will print extra pages. I was hoping when I deleted the unused formulas, when I printed it would only print the pages that had information. This is not the case and I really need help to find a solution. I have tried using this formula and it is not working and prints three extra pages that I do not need even with the extra formulas being deleted. Sub selectonly() ' ' selectonly Macro ' Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)" End Sub
This comment was minimized by the moderator on the site
thank you so much. That save me a lot of time
This comment was minimized by the moderator on the site
Trying to find a delete Function that will delete entire designated rows automatically, based on certain values or certain text contained in other cell(s), using only automated formulas.
This comment was minimized by the moderator on the site
How can i delete selected cell that i want based on value that i entered for example : A1 B1 C1 D1 A2 B2 C2 D2 A3 B3 C3 D3 A4 B4 C4 D4 When i entered in some cell for example "2" then 2 row will be deleted from A3:D4. If i entered "1" then 1 row will be deleted from A4:D4. if i entered "3" then 3 row will be deleted from A2:D4
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