循环浏览Excel Excel VBA

Sergey Ryabov 发表于 Dev

谢尔盖·里亚博夫（Sergey Ryabov）

如果没有减号，我需要遍历现有公式并删除整行。是否可以通过char逐个遍历char公式，或者有另一种方法可以识别公式中是否存在“-”？

Sub clearvalidation()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Sheets
    Set vali = ws.Cells.Find("Validation", LookIn:=xlValues, LookAt:=xlPart)
    If Not vali Is Nothing Then
        fr = vali.Row + 2
        lr = ws.Cells(fr + 1, 6).End(xlDown).Row
        For exclrow = fr To lr
            For Each char In ws.Cells(exclrow, 7)
                If char = "-" Then
                    check = "ok"
                    Exit For
                End If
            Next char
            If check <> ok Then check = "delete"
            Select Case check
                Case Is = "ok"
                    Stop
                Case Is = "delete"
                    Stop
            End Select
        Next exclrow
    End If
    vali = Empty
Next ws

End Sub

R3uK

Instr如果未找到要查找的子字符串，则应使用它将返回0；如果找到该子字符串的索引，则应使用它将返回0 。

并且，当您循环删除行时，应使用从下到上的顺序，Step -1以避免丢失某些行（如果删除了第i行，则第i + 1行将成为新的第i行，而在下一行之后您会想念它）。

Sub clearvalidation()
Dim ws As Worksheet, _
    FirstAddress As String, _
    cF As Range


For Each ws In ActiveWorkbook.Sheets
    ws.Range("A1").Activate
    With ws.Cells
        Set vali = .Find("Validation", LookIn:=xlValues, LookAt:=xlPart)
        If Not vali Is Nothing Then
            FirstAddress = vali.Address
            'If there is a result, keep looking with FindNext method
            Do
                fr = vali.Row + 2
                lr = ws.Cells(fr + 1, 6).End(xlDown).Row
                For exclrow = lr To fr Step -1
                    If InStr(1, ws.Cells(exclrow, 7).Formula, "-") Then
                        ws.Cells(exclrow, 7).EntireRow.Delete shift:=xlUp
                    Else
                    End If
                Next exclrow
                Set vali = .FindNext(vali)
            'Look until you find again the first result
            Loop While Not vali Is Nothing And vali.Address <> FirstAddress
        End If
        vali = Empty
    End With
Next ws


End Sub