"entirerow.delete"跳过For循环中的条目 [英] "entirerow.delete" skips entries in For loop

问题描述

我正在尝试清理一组数据，并发现vba函数wholerow.delete有点奇怪 如果行格式为删除线，则以下代码将按预期删除整行，但如果行也是该格式，则将跳过紧随其后的行.似乎需要使用非删除线格式的行来重置"删除更多行的功能.有谁知道为什么，或者我可以做些什么来调试呢?

I'm trying to clean up a set of data and noticed something strange with vba function entirerow.delete The following code will, as intended, delete the entire row if it is in the format strikethrough, but will skip the rows immediately following it, if they are also in that format. It seems like a it takes a row that is not in the strikethrough format to "reset" the ability to delete more rows. Does anyone know why, or what I could do to debug this?

For Each rng In rng1
'Check each character in the cell
    For i = 1 To Len(rng.Value)
'If any letter is Strikethrough,delete entire column
        If rng.Characters(i, 1).Font.Strikethrough = True Then
            rng.Select    'just serves the purpose of observing which rows are being selected
            rng.EntireRow.Delete
        GoTo NextRng
        End If
    Next i
NextRng:
Next rng

我应该说我已经找到了使用另一种方法的解决方法，但这很慢:

I should say that I have found a workaround using a different approach, but it is very slow:

'Delete cells that have the strikethrough format - works but is super slow!
ws2.Range("B2").Activate
Do Until ActiveCell.Value = ""
    If ActiveCell.Font.Strikethrough = True Then
        ActiveCell.EntireRow.Delete
        Else: ActiveCell.Offset(1, 0).Activate
    End If
Loop

如果任何人都可以快速解决该问题，我也非常感谢您的投入.

If anyone has an alternative method to solve this issue that is also fast, I'd also be immensely grateful for your input.

推荐答案

感谢您对我的所有快速答复.特别感谢@Siddarth Rout在这里在这个线程上推动我(一种稍微更快)的方法: VBa条件删除循环不起作用 这是工作代码，以防万一有人好奇:

Thanks to all of your quick responses I figured it out. Special thanks @Siddarth Rout for nudging me towards a (slightly) quicker method on this thread here: VBa conditional delete loop not working Here's the working code in case anyone is curious:

Dim delRange As Range
Dim ws2 As Worksheet
Dim i As Long

'Find Lastrow in ws2
LastRow2 = ws2.Cells.Find(What:="*", _
                After:=Range("A1"), _
                LookAt:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Row
With ws2
    For i = 1 To LastRow2
        If .Cells(i, 2).Font.Strikethrough = True Then
'This if statement adds all the identified rows to the range that will be deleted
            If delRange Is Nothing Then
                Set delRange = .Rows(i)
            Else
                Set delRange = Union(delRange, .Rows(i))
            End If
        End If
    Next i

    If Not delRange Is Nothing Then delRange.Delete
End With

这篇关于"entirerow.delete"跳过For循环中的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！