在Excel中删除特定行 [英] Delete Specific rows in Excel

问题描述

我想创建一个for循环来检查工作表中的所有行，并希望此代码能够删除行（如果它们在某些列中包含指定的内容）（即，如果列K包含六月

I want to create a for loop to check all of the rows in a sheet that I have and want this code to be able to delete rows if they contain a specified content in certain columns (i.e. if column K contains "June" delete the row. Is there a way to code this?

*编辑
我有代码可以在一行中搜索条件，但是现在我需要它根据两列中的数据搜索和删除行，即如果K列中的数据与单元格AJ1相匹配（已经有）并且J列中的数据与AK1相匹配，则删除这些行。

*Edit I have the code working to search for criteria in one column, but now I need it to search and delete rows based on the data in two columns. i.e. If the data in column K matches cell AJ1 (already have) and the data in column J matches AK1, then delete these rows.

我的代码是这样的：

Sub DeleteRows（）

Sub DeleteRows()

Sheets("Sheet1").Select
Dim rFind As Range
Dim rDelete As Range
Dim strSearch As String
Dim iLookAt As Long
Dim bMatchCase As Boolean

strSearch = Range("AJ1")


iLookAt = xlWhole
bMatchCase = False

Set rDelete = Nothing

Application.ScreenUpdating = False

With Sheet1.Columns("K:K")

    Set rFind = .Find(strSearch, LookIn:=xlValues, LookAt:=iLookAt, SearchDirection:=xlPrevious, MatchCase:=bMatchCase)
    If Not rFind Is Nothing Then
        Do
            Set rDelete = rFind
            Set rFind = .FindPrevious(rFind)
            If rFind.Address = rDelete.Address Then Set rFind = Nothing
            rDelete.EntireRow.Delete
        Loop While Not rFind Is Nothing
    End If
End With
Application.ScreenUpdating = True

结束子

推荐答案

拇指，如果可能，应避免在Excel VBA中循环单元格。遍历单元格是缓慢而低效的。鉴于程序的范围，这可能并不重要，但这是需要考虑的事情。如果您不熟悉VBA编程，那么尽早养成良好的习惯就尤其重要。

As a general rule of thumb, you should avoid looping over cells if possible in Excel VBA. Looping over cells is slow and inefficient. It may not matter given the scope of your program, but it is something to be considered. If you are new to VBA programming, it's especially important to pick up good habits early on.

这里是使用 Range.Find 方法（ MSDN参考）收集要删除的行范围，然后在一条语句中将其全部删除。

Here is a solution using the Range.Find method (MSDN reference) to gather the range of rows to delete, and then delete them all in one statement.

Sub DeleteRows()

    Dim rngResults As Range, rngToDelete As Range
    Dim strFirstAddress As String

    With Worksheets("Sheet1").UsedRange 'Adjust to your particular worksheet

        Set rngResults = .Cells.Find(What:="June") 'Adjust what you want it to find
        If Not rngResults Is Nothing Then

            Set rngToDelete = rngResults

            strFirstAddress = rngResults.Address

            Set rngResults = .FindNext(After:=rngResults)

            Do Until rngResults.Address = strFirstAddress
                Set rngToDelete = Application.Union(rngToDelete, rngResults)
                Set rngResults = .FindNext(After:=rngResults)
            Loop

        End If

    End With

    If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete

    Set rngResults = Nothing

End Sub

这篇关于在Excel中删除特定行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！