For循环在excel VBA中没有完全循环 [英] For Loop not fully cycling in excel VBA

问题描述

我有一个宏，我可以在其中搜索一行中的文本，如果一列没有我指定的文本，它就会被删除.这是我的代码:

I have a macro where I search for text in a row and if a column does not have my specified text it is deleted. Here is my code:

Private Sub Test()

Dim lColumn As Long
    lColumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column


Dim i As Long
Dim myCell As Range
Dim myRange As Range
Set myRange = Worksheets("2019").Range(Cells(2, 1), Cells(2, lColumn))

For Each myCell In myRange
  If Not myCell Like "*($'000s)*" And Not myCell Like "*Stmt Entry*" And Not myCell Like "*TCF*" And_ 
  Not myCell Like "*Subtotal*" And Not myCell Like "*Hold*" Then
    myCell.EntireColumn.Select
    Selection.Delete
  End If

Next

End Sub

我的问题是，当我执行宏时，它只会删除一些列，而不是范围末尾的列.如果我然后再次运行宏，它将成功删除我要求它删除的所有列.

My issue is that when I execute the macro it will only delete some of the columns but not the ones towards the end of the range. If I then run the macro again it will successfully delete all the columns I ask it to.

如果我将宏切换到 - 比方说 - 使单元格加粗而不是删除它们，它每次都能完美运行.

If I switch the macro to- let's say- make the cells bold instead of deleting them it works perfectly every time.

我错过了什么?

非常感谢！

推荐答案

尽管在这个 & 中每个人都说只是向后循环"链接的帖子，这不是您想要做的.

Despite everyone saying "just loop backwards" in this & linked posts, that's not what you want to do.

它会起作用，然后你的下一个问题将是我怎样才能加速这个循环".

It's going to work, and then your next question will be "how can I speed up this loop".

真正的解决方案是停止您正在做的事情，并以不同的方式做事.在迭代时修改集合从来都不是一个好主意.

The real solution is to stop what you're doing, and do things differently. Modifying a collection as you're iterating it is never a good idea.

从一个可以将两个范围合并为一个的辅助函数开始:

Start with a helper function that can combine two ranges into one:

Private Function CombineRanges(ByVal source As Range, ByVal toCombine As Range) As Range
    If source Is Nothing Then
        'note: returns Nothing if toCombine is Nothing
        Set CombineRanges = toCombine
    Else
        Set CombineRanges = Union(source, toCombine)
    End If
End Function

然后声明一个 toDelete 范围并使用这个 CombineRanges 函数来构建(选择")一个 Range在您进行迭代时 - 请注意，此循环不会修改任何地方的任何单元格:

Then declare a toDelete range and use this CombineRanges function to build ("select") a Range while you're iterating - note that this loop does not modify any cells anywhere:

Dim sheet As Worksheet
' todo: use sheet's codename instead if '2019' is in ThisWorkbook
Set sheet = ActiveWorkbook.Worksheets("2019")

Dim source As Range
' note: qualified .Cells member calls refer to same sheet as .Range call
Set source = sheet.Range(sheet.Cells(2, 1), sheet.Cells(2, lColumn))

Dim toDelete As Range
Dim cell As Range
For Each cell In source
    'note: needed because comparing cell.Value with anything will throw error 13 "type mismatch" if cell contains a worksheet error value.
    'alternatively, use cell.Text.
    If Not IsError(cell.Value) Then
        If Not cell.Value Like "*($'000s)*" _
            And Not cell.Value Like "*Stmt Entry*" _
            And Not cell.Value Like "*TCF*" _
            And Not cell.Value Like "*Subtotal*" _
            And Not cell.Value Like "*Hold*" _
        Then
            Set toDelete = CombineRanges(cell, toDelete)
        End If
    End If
Next

最后一步是删除 toDelete 范围的 .EntireColumn...如果它不是 Nothing要点:

The last, final step is to delete the .EntireColumn of the toDelete range... if it isn't Nothing at that point:

If Not toDelete Is Nothing Then toDelete.EntireColumn.Delete

这篇关于For循环在excel VBA中没有完全循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！