VBA macro: Select sheets and delete rows based on a condition

Question

I'm trying to write a VBA Macro, but I'm new to this. (And I'm german, sorry for my bad english). I found some useful Code snippets here on StackOverflow, but I can't adapt it for my Needs. I have a workbook with several sheets, and some of them have the same structure. In These sheets in column C, all have a date. The macro should look in These sheets, if the date is like "00.01.1900" and then delete this row. I tried two versions, but None of them worked. It just happend Nothing, so Maybe the walkthrough of the sheets is wrong? Or the string matching does not work?

Version 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

Version 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

Thanks a lot in Advance!

OMG sorry, after posting I saw, that the heading is wrong. How can I Change it? — bluebird, Nov 12 '18 at 09:37
Thank you @PEH, I have edited it. Is the heading useful now? — bluebird, Nov 12 '18 at 09:41
Your second approach looks fine to me: But you should always specify the worksheet for `Cells` and `Rows` like `wks.Cells` and `wks.Rows` otherwise excel does not know which sheet you mean. (same for finding the last row). — Pᴇʜ, Nov 12 '18 at 09:44

bluebird · Answer 1 · 2018-11-12T10:14:12.373

Edit: changed .value to .value2 and inserted "Exit for"

Thanks a lot, this works now:

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

I suggest to add an `Exit For` right after `Next t` and before `End If` to make it a bit faster. You don't need to check all the other names if you found the sheet already in your array. — Pᴇʜ, Nov 12 '18 at 09:55

score 0 · Answer 2 · answered Nov 12 '18 at 09:45

Go to "Home" and select one of the 00.01.1900 cell and change its format from date to general, do you see it becomes 0?

Need to know whether the cell is storing a value 0 or a text "00.01.1900"

If it does change to 0, just use your version 2 code but changed below row (from "00.01.1900" to 0)

If Cells(t, 3).Value = 0 Then

score 0 · Accepted Answer · answered Nov 12 '18 at 09:55

Assuming that your data column C contains real dates (and not strings), use your 2nd variant but check for a (numeric) value 0. The string "00.01.1900" is just a (language dependent) representation for 0 as date.

Be sure to access Cells(t, 3).Value2 (not Value as this will return a string when the cell is formatted as Date). See What is the difference between .text, .value, and .value2? for some details.

Note that when you loop over data with the goal to delete some of it, you always should work backwards (as the second example does), else you risk to miss some: Let's say you remove row 3, then your previous row 4 will get the new row 3, but your loop will continue with checking the row 4 - the former row 4 (and now row 3) will never be checked.

Thanks for the link, I changed it to .Value2 now – bluebird Nov 12 '18 at 10:12 — bluebird, Nov 12 '18 at 10:12

VBA macro: Select sheets and delete rows based on a condition

3 Answers3