提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

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

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

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

使用VBA代码基于一个单元格值删除行

根据一个或两个单元格值删除行

根据多个单元格值删除行

按特定单元格值快速选择整个行/列,然后轻松删除选定的行/列!

Kutools for ExcelSelect SpecifiC Cells 实用程序为Excel用户提供了一个简单的选择,如果单元格值与Excel中的某个值匹配,则可以选择整行或整列。 工作更容易,更清晰! 全功能免费试用30天!
如果包含特定值,则选择特殊单元格选择整个行列

Office选项卡在Office中启用选项卡式编辑和浏览,使您的工作更轻松......
Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%
  • 重用任何东西: 将最常用或最复杂的公式,图表和其他任何内容添加到您的收藏夹中,并在将来快速重复使用它们。
  • 超过20文本功能: 从文本字符串中提取数字; 提取或删除部分文字; 将数字和货币转换为英文单词。
  • 合并工具:将多个工作簿和工作表合二为一; 合并多个单元格/行/列,而不会丢失数据; 合并重复的行和总和。
  • 拆分工具:根据价值将数据分割成多个工作表; 一本工作簿可转换为多个Excel,PDF或CSV文件; 一列到多列。
  • 粘贴跳过 隐藏/过滤行; 数和总和 按背景颜色; 将个性化电子邮件批量发送给多个收件人。
  • 超级过滤器: 创建高级过滤方案并应用于任何工作表; 排序 按周,日,频率等; 筛选 通过大胆,公式,评论......
  • 超过300强大的功能; 与Office 2007-2019和365一起使用; 支持所有语言; 在您的企业或组织中轻松部署。


箭头蓝色右泡 使用“查找和替换”功能根据单元格值删除行

在Excel中,您可以应用强大的查找和替换功能轻松删除基于特定单元格值的行。 请做如下操作:

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

2。 在查找和替换对话框中,输入特定的单元格值(在我们的例子中,我们输入 ) 进入 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()
'Updateby20140314
Dim rng As Range
Dim InputRng As Range
Dim DeleteRng As Range
Dim DeleteStr As String
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
DeleteStr = Application.InputBox("Delete Text", xTitleId, Type:=2)
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)
        End If
    End If
Next
DeleteRng.EntireRow.Delete
End Sub

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

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

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

然后你会看到整个行已经根据指定的值被删除了。


箭头蓝色右泡 使用Kutools for Excel根据一个或两个单元格值删除行

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

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

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

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

备注: 这个 Select Specific Cells 功能支持通过一个或两个特定的值删除行。 要根据两个指定的值删除行,请在中指定另一个值 Specific type 选择特定单元格对话框部分,如下面的截图所示:

Kutools for Excel -包括多个用于Excel的300便捷工具。 全功能免费试用30天,无需信用卡! 现在加入


箭头蓝色右泡使用Kutools for Excel删除基于多个单元格值的行

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

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 包含多个用于Excel的300便捷工具,可以在30天内不受限制地免费试用。 立即下载并免费试用!

Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能30天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
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.
    kaushal · 2 years ago
    very well. Thank you!
  • To post as a guest, your comment is unpublished.
    Bobby · 2 years ago
    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
  • To post as a guest, your comment is unpublished.
    Amar · 2 years ago
    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..
  • To post as a guest, your comment is unpublished.
    Marion · 2 years ago
    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

    [i][b]My real case is:[/b][/i] 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
  • To post as a guest, your comment is unpublished.
    Aju Thoas · 3 years ago
    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.
  • To post as a guest, your comment is unpublished.
    PC · 3 years ago
    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
  • To post as a guest, your comment is unpublished.
    NEU · 3 years ago
    thank you so much. That save me a lot of time
  • To post as a guest, your comment is unpublished.
    NC Arch · 4 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Clay · 4 years ago
    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
  • To post as a guest, your comment is unpublished.
    Milon · 4 years ago
    Great it work thank you so much
  • To post as a guest, your comment is unpublished.
    Moe · 4 years ago
    Hello, many thanks for the code. But say I want to delete one row containing "Apple". And not all of the rows containing it. Let's just say the last one, or a random one, doesn't really matter, just one. Many thanks in advance! :-)
  • To post as a guest, your comment is unpublished.
    Meredith · 4 years ago
    Just what I needed, thanks! :D
  • To post as a guest, your comment is unpublished.
    JonS · 4 years ago
    Is there anyway to have the value recognize a value that has expired, such as. All rows that show an expiration date within a specific column will be automatically removed and then the rows that were deleted will be filled by moving the rows beneath. Filling gaps.
  • To post as a guest, your comment is unpublished.
    angel baby · 4 years ago
    What a great. thank so all so much. :)
  • To post as a guest, your comment is unpublished.
    DavidGough · 4 years ago
    is it possible to compare two columns and then delete? is in range A3:D3000 if cell A=x and cell B=y then delete row?
  • To post as a guest, your comment is unpublished.
    DavidGough · 4 years ago
    How can I specify the range A3:D3000 (ie. I don´t want the user to select the range)? How can I make the Delete String a cell reference, ex. G1?
  • To post as a guest, your comment is unpublished.
    Joan K · 4 years ago
    Hello and thank you very much for the VBA code. I have one question.

    Is it possible to make the code the way that it is possible to choose several cell values to delete at the same time? For example choose Apple and Emily at the same time and delete them?
    I appreciate you work and looking forward to your response.

    Joan K
  • To post as a guest, your comment is unpublished.
    Victor · 5 years ago
    i have a list of stores in one document. I have a report that returns thousand of lines. the store number is always in Column A. Is there a code/macro that will search the report and delete all columns with store numbers from the store list. thinking a vlookup and "Do While" are needed.
  • To post as a guest, your comment is unpublished.
    alex2 · 5 years ago
    I'd like to delete all rows where column1 = "Apple" AND column3 = "green". please?
    • To post as a guest, your comment is unpublished.
      Finidi · 5 years ago
      Im not sure how to do this using macro's or so, but one option that would do its job just fine is to add an extra colum that tests for the one colum to be "apple" and the other to be "green" and then let it give a simple "yes" or "no" value. than use either of the above options to search for the value "yes" and remove rows based on that instead of 2 seperate values.
  • To post as a guest, your comment is unpublished.
    gabz · 5 years ago
    thank.. it helps me a lot...
  • To post as a guest, your comment is unpublished.
    smj2013 · 5 years ago
    Thank you for the VB Script. I can not wait to try this. Question:Is there anyway to mark the columns for deletion prior to deleting them and/or copy to a separate tab in the workbook? In addition is there a way to run this script run on multiple workbooks/files at one time?
  • To post as a guest, your comment is unpublished.
    Leo · 5 years ago
    Hi,
    Any way to write it to delete a row, with an OR exception? I.e- delete rows that are duplicates, but ignore a certain value. I want to delete duplicates, except where the column in question contains a blank b/c that column doesn't have valid data yet. I ran as you have, but I ended deleting rows with a blank in the column in question, so I can't use it as it is.
    Thanks
  • To post as a guest, your comment is unpublished.
    Tee · 5 years ago
    Hi,
    What is the easiest way to delete rows that do NOT contain "Apple" please?

    Thanks
  • To post as a guest, your comment is unpublished.
    Aleardo · 5 years ago
    Thanks, this is great!
    Quick comment, I tried the find and replace function in excel 2010.
    In order to select all the found results I had to use Ctrl +A instead of Alt + A.
  • To post as a guest, your comment is unpublished.
    Mira · 5 years ago
    Definitely awsome! Appreciate for sharing!!!
  • To post as a guest, your comment is unpublished.
    Joe Nelson · 5 years ago
    I just wanted to say thank you. This worked like a charm.
  • To post as a guest, your comment is unpublished.
    Adam · 5 years ago
    Lets say I put the below code in to delete all of my rows that contain apple but then I want it to continue once that is done and delete all the rows that contain banana? I tried to just duplicate the code but it seems to stop after apple.

    thanks.

    Sub Delete_Rows()
    Dim rng As Range, cell As Range, del As Range
    Set rng = Intersect(Range("A1:C20"), ActiveSheet.UsedRange)
    For Each cell In rng
    If (cell.Value) = "Apple" _
    Then
    If del Is Nothing Then
    Set del = cell
    Else: Set del = Union(del, cell)
    End If
    End If
    Next cell
    On Error Resume Next
    del.EntireRow.Delete
    End Sub
  • To post as a guest, your comment is unpublished.
    H man · 5 years ago
    I get:

    run-time error '13':
    Type mismatch

    Debugging highlights the If (cell.Value) = "FALSE" _
    Then
    section.

    I'm totally lost in MVB, any help would be appreciated.
  • To post as a guest, your comment is unpublished.
    Jim Mc · 5 years ago
    Heartfelt thanks - it's always amusing when the Marketing guy tries to write code, and this info was super helpful. I was unable to get the code to span multiple columns ("AA2:AA3000" works, "AB2:AB3000" works, but "AA2:AB3000" doesn't work. There's no Earth-shattering Kaboom - it just does nothing. I've solved it (amateurishly, I suppose) by running several macros in sequence, but there's probably a more elegant way. Thanks again for your help, Jim (Melville, NY)
  • To post as a guest, your comment is unpublished.
    Claire · 5 years ago
    Hey - thanks so much for the script. I was wondering if you can use wildcards so you can select anything within a cell that matches rather than a specific item? I've tried using ** wildcards but it doesn't seem to do anything. Here is what I have:

    Sub Delete_Rows()
    Dim rng As Range, cell As Range, del As Range
    Set rng = Intersect(Range("B6:B20"), ActiveSheet.UsedRange)
    For Each cell In rng
    If cell.Value Like "*WORDTODELETE*" _
    Then
    If del Is Nothing Then
    Set del = cell
    Else: Set del = Union(del, cell)
    End If
    End If
    Next cell
    On Error Resume Next
    del.EntireRow.Delete
    End Sub
  • To post as a guest, your comment is unpublished.
    Art · 6 years ago
    Bobby, try this, it should get what you want:
    If (cell.Value) = "Apple" OR (cell.Value) = "Monday"
    • To post as a guest, your comment is unpublished.
      April · 5 years ago
      Is there anyway to have the value recognize a value whether is > or = to a certain value?
  • To post as a guest, your comment is unpublished.
    Bobby · 6 years ago
    Any way to modify the If (cell.Value) = "Apple" to include multiple values?
  • To post as a guest, your comment is unpublished.
    Alan · 6 years ago
    Fantastic! That worked a treat, I altered it slightly to suit what I needed but pure genious. Oh VBA Version.