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. 添加Option Explicit
2. 添加Option Explicit
3. 使用工作表限定所有对象(Range)

1. Add Option Explicit
2. Add Option Explicit
3. Qualify all objects (Range) with a worksheet

---

出于某些原因，应避免在循环时删除行.主要原因是它可能效率很低.举例来说，假设您有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以加快速度

---

For learning purposes, this is how you would loop backwards. It has less lines than the above method, but is less efficient. If you do decide to go this route, be sure to toggle off ScreenUpdating to speed things up

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屋！