时间戳

首页 > 解决方案 > VBA宏：根据条件选择工作表并删除行

excel - VBA宏：根据条件选择工作表并删除行

问题描述

我正在尝试编写一个 VBA 宏，但我对此并不陌生。（我是德国人，抱歉我的英语不好）。我在 StackOverflow 上找到了一些有用的代码片段，但我无法适应我的需要。我有一个包含几张工作表的工作簿，其中一些具有相同的结构。在 C 列的这些工作表中，所有工作表都有一个日期。如果日期类似于“00.01.1900”，则宏应在这些工作表中查找，然后删除该行。我尝试了两个版本，但都没有工作。它只是发生了什么，所以也许床单的演练是错误的？还是字符串匹配不起作用？

版本 1：

    Dim str As String, w As Long, m As Variant, wss As Variant


        wss = Array("Schritt3-WEA1", "Schritt3-WEA2", "Schritt3-WEA3", "Schritt3-WEA4", _
            "Schritt3-WEA5", "Schritt3-WEA6", "Schritt3-WEA7", "Schritt3-WEA8", "Schritt3-WEA9" _
            , "Schritt3-WEA15", "Schritt3-WEA16", "Schritt3-WEA17", "Schritt3-WEA18", _
            "Schritt3-WEA19", "Schritt3-WEA20", "Schritt3-WEA21", "Schritt3-WEA22", _
            "Schritt3-WEA23", "Schritt3-WEA28", "Schritt3-WEA29", "Schritt3-WEA36")
        str = "00.01.1900"
        If CBool(Len(str)) And str <> "False" Then
            With ThisWorkbook
                For w = LBound(wss) To UBound(wss)
                    With .Worksheets(wss(w))

                        m = Application.Match(str, .Columns(3), 0)
                        Do While Not IsError(m)
                            .Cells(m, "A").EntireRow.Delete
                            m = Application.Match(str, .Columns(3), 0)
                        Loop
                    End With
                Next w
             End With
        End If

版本 2：

        Dim wks As Worksheet
        Dim arrSheets As Variant
        Dim iShCount As Integer
        arrSheets = Array("Schritt3-WEA1", "Schritt3-WEA2", "Schritt3-WEA3", "Schritt3-WEA4", _
            "Schritt3-WEA5", "Schritt3-WEA6", "Schritt3-WEA7", "Schritt3-WEA8", "Schritt3-WEA9" _
            , "Schritt3-WEA15", "Schritt3-WEA16", "Schritt3-WEA17", "Schritt3-WEA18", _
            "Schritt3-WEA19", "Schritt3-WEA20", "Schritt3-WEA21", "Schritt3-WEA22", _
            "Schritt3-WEA23", "Schritt3-WEA28", "Schritt3-WEA29", "Schritt3-WEA36")
        For Each wks In Worksheets
            For iShCount = 0 To UBound(arrSheets)
                If wks.Name = arrSheets(iShCount) Then
                    '** Ermittlung der letzten Zeile in Spalte C
                    lz = Cells(Rows.Count, 3).End(xlUp).Rows.Row
                    '** Durchlauf aller Zeilen
                    For t = lz To 15 Step -1
                    'Z?hlung r?ckw?rts bis Zeile 15
                    'Abfragen, ob in der dritten Spalte "00.01.1900" steht
                        If Cells(t, 3).Value = "00.01.1900" Then
                            Rows(t).Delete Shift:=xlUp
                        End If
                    Next t
                End If
            Next
        Next

非常感谢提前！

标签： excelvba

解决方案

编辑：将 .value 更改为 .value2 并插入“退出”

非常感谢，现在可以了：

Dim wks As Worksheet
Dim arrSheets As Variant
Dim iShCount As Integer
arrSheets = Array("Schritt3-WEA1", "Schritt3-WEA2", "Schritt3-WEA3", "Schritt3-WEA4", _
    "Schritt3-WEA5", "Schritt3-WEA6", "Schritt3-WEA7", "Schritt3-WEA8", "Schritt3-WEA9" _
    , "Schritt3-WEA15", "Schritt3-WEA16", "Schritt3-WEA17", "Schritt3-WEA18", _
    "Schritt3-WEA19", "Schritt3-WEA20", "Schritt3-WEA21", "Schritt3-WEA22", _
    "Schritt3-WEA23", "Schritt3-WEA28", "Schritt3-WEA29", "Schritt3-WEA36")
For Each wks In Worksheets
    For iShCount = 0 To UBound(arrSheets)
        If wks.Name = arrSheets(iShCount) Then
            '** Ermittlung der letzten Zeile in Spalte C
            lz = wks.Cells(Rows.Count, 3).End(xlUp).Rows.Row
            '** Durchlauf aller Zeilen
            For t = lz To 15 Step -1
            'Z?hlung r?ckw?rts bis Zeile 15
            'Abfragen, ob in der dritten Spalte "00.01.1900" steht
                If wks.Cells(t, 3).Value2 = 0 Then
                    wks.Rows(t).Delete Shift:=xlUp
                End If
            Next t
            Exit For
        End If
    Next
Next

推荐阅读