Excel VBA删除行如果Mispelled Word [英] Excel VBA Delete Row If Mispelled Word

问题描述

我正在尝试删除整个行，如果单元格包含拼写错误的单词。如果一行被删除，则会跳过一行，从而丢失可能直接发生的拼写错误的单词。

I am trying to delete the entire row, if a cell contains a misspelled word. If a row is removed, is skips a row, thus missing any misspelled words that may occur directly after another.

以下是代码：

Sub DeleteMispelledCells()
For Each cl In ActiveSheet.UsedRange
If Not Application.CheckSpelling(Word:=cl.Text) Then _
cl.EntireRow.Delete
Next cl
End Sub

我明白问题是cl没有考虑到当前的行被删除，但我不知道如何纠正这个。我也知道，如果循环从最后一行到第一行，而不是第一个到最后一行，那么它也将被更正。但是，我也不知道如何做到这一点。

I understand the issue is the cl is not taking into account that the current row was removed, but I do not know how to correct this. I am also aware that if the loop ran from the last row to the first, instead of the first to the last, that it would also be corrected. However, I do not know how to do this either.

推荐答案

为了循环遍历每一行，做这样的事情：

In order to loop through every row in reverse order you'll do something like this:

Sub DeleteMispelledCells()
  Dim lRow As Long, cl As Range

  With ActiveSheet
    For lRow = .UsedRange.Rows.Count To 1 Step -1
      For Each cl In .Rows(lRow)
        If Not IsEmpty(cl) Then
          If Not Application.CheckSpelling(Word:=cl.Text) Then
            cl.EntireRow.Delete
            Exit For
          End If
        End If
      Next cl
    Next lRow
  End With
End Sub

我不是100％确定我已经掌握了所有的语法，因为我不在我可以测试的计算机上，但至少应该让你在某个地方开始。

I am not 100 % sure I've gotten all of the syntax right, as I am not on a computer at which I can test it, but it should at least give you somewhere to start.

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