VBA 使用 Like 运算符删除行 [英] VBA Deleting rows with the Like operator

问题描述

简单的事情并不简单.我正在尝试根据具有以2L"开头的数据的特定列删除行.所以我写了这段代码(LastRow是理解的):

Simple thing is not simple. I am trying to delete rows based on a specific column having data that begins with "2L". So I wrote this code (LastRow is understood):

Sub Cleanup()

For i = 1 To LastRow
    If Range("F" & i) Like "2L*" Then Rows(i).delete
Next i
End Sub

问题是它确实删除了行.我不知道删除行的标准是什么，但我知道它不会让每一行的单元格在该列中以2L"开头.我从 1100 行开始，然后下降到 677.

The problem is that it does delete rows. I have no idea what it's criteria is for deleting rows but I know it does not get every row with a cell that starts with "2L" in that column as it was written. I start out with 1100 row and it goes down to 677.

我没有创建我正在编写脚本的 excel.我唯一的线索是我确实尝试格式化列，当格式窗口打开时，它们没有单一类型.在将代码运行为 Text 之前，我确实尝试格式化数据列，但它似乎没有成功.

I did not create the excel that I am scripting in. the only clue I have is that I did try to format the column and when the format window opens, they don't have a single type. I did try formatting the column of data before I ran the code to be Text but it did not seem to do the trick.

想法?

推荐答案

首先，一些强制性的家务.....

First, some mandetory house keeping.....

1. 添加选项显式
2. 添加选项显式
3. 使用工作表限定所有对象(Range)

<小时>

出于某些原因，您应该避免在循环时删除行.主要原因是它可能非常低效.例如，假设您有 500 个 Like "2L*" 单元格.这意味着您将有 500 次迭代被删除的行.

---

You should avoid deleting rows as you loop for a few reasons. The main reason is that it can be highly inefficient. Say, for instance, that you have 500 cells that are Like "2L*". That means you will have 500 iterations of rows being deleted.

相反，将 Like "2L*" 的每个实例添加到单元格的 Union(集合)，一旦循环完成，一次性删除整个 Union.现在您只有 1 个被删除的行实例.

Instead, add every instance of Like "2L*" to a Union (collection) of cells and once your loop is complete, delete the entire Union all at once. Now you just have 1 instance of rows being deleted.

避免在循环中删除行的另一个原因是它迫使您向后循环.这没有什么错，它只是往往给人们带来困难，因为它起初并不直观.删除一行时，会将循环的范围向上移动，这会导致跳过行.下面的方法不需要向后循环.

Another reason to avoid deleting rows inside your loop is that it forces you to loop backwards. There is nothing wrong with this, it just tends to give people a hard time since it is not intuitive at first. When you delete a row, you shift the range you are looping through up and this causes rows to be skipped. The method below does not need to be looped backwards.

Option Explicit

Sub Cleanup()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("???")
Dim i As Long, DeleteMe As Range

'Gather up union of CELLS like 2L*
For i = 1 To ws.Range("F" & ws.Rows.Count).End(xlUp).Row
    If ws.Range("F" & i) Like "2L*" Then
        If DeleteMe Is Nothing Then
            Set DeleteMe = ws.Range("F" & i)
        Else
            Set DeleteMe = Union(DeleteMe, ws.Range("F" & i))
        End If
    End If
Next i

'Delete collection of cells here (if any exist)
If Not DeleteMe Is Nothing Then DeleteMe.EntireRow.Delete

End Sub

<小时>

出于学习目的，这就是您向后循环的方式.它的行数比上述方法少，但效率较低.如果您决定走这条路线，请务必关闭 ScreenUpdating 以加快速度

Sub CleanUp2()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("???")
Dim i As Long

For i = ws.Range("F" & ws.Rows.Count).End(xlUp).Row to 1 Step -1 '<===== Backwards!
    If ws.Range("F" & i) Like "2L*" Then ws.Rows(i).Delete
Next i

End Sub

这篇关于VBA 使用 Like 运算符删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！