Deleting 4 rows because of 1 item

[SoNaive]

Hello, i'm trying to figure out a way to delete multiple rows in a large spreadsheet with automation
Example:
<table width="819" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:9984; width:205pt" span="3" width="273"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl72" style="height:15.0pt;width:205pt" width="273" height="20">84076</td> <td class="xl65" style="width:205pt" width="273">Cantor</td> <td class="xl65" style="border-left:none;width:205pt" width="273">10</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;width:205pt" width="273" height="20">Retail</td> <td class="xl68" style="border-left:none;width:205pt" width="273">Tue Nov 08, 2011</td> <td class="xl68" style="border-left:none;width:205pt" width="273">160</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:205pt" width="273" height="20">REVISED</td> <td class="xl69" style="border-left:none;width:205pt" width="273">Division 8</td> <td class="xl68" style="border-left:none;width:205pt" width="273">1600</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;width:205pt" width="273" height="20"> </td> <td class="xl70" style="border-left:none;width:205pt" width="273">Compulsion Done</td> <td class="xl71" style="border-left:none;width:205pt" width="273"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl72" style="height:15.0pt" height="20">84077</td> <td class="xl65" style="border-top:none;width:205pt" width="273">Cantor</td> <td class="xl65" style="border-top:none;border-left:none;width:205pt" width="273">10</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;width:205pt" width="273" height="20">Retail</td> <td class="xl68" style="border-left:none;width:205pt" width="273">Thu Nov 17, 2011</td> <td class="xl68" style="border-left:none;width:205pt" width="273">160</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:205pt" width="273" height="20">CANCELLED</td> <td class="xl69" style="border-left:none;width:205pt" width="273">Division 8</td> <td class="xl68" style="border-left:none;width:205pt" width="273">1600</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;width:205pt" width="273" height="20"> </td> <td class="xl70" style="border-left:none;width:205pt" width="273">Compulsion Done</td> <td class="xl71" style="border-left:none;width:205pt" width="273"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl72" style="height:15.0pt" height="20">84078</td> <td class="xl65" style="border-top:none;width:205pt" width="273">Cantor</td> <td class="xl65" style="border-top:none;border-left:none;width:205pt" width="273">10</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;width:205pt" width="273" height="20">Retail</td> <td class="xl68" style="border-left:none;width:205pt" width="273">Thu Oct 13, 2011</td> <td class="xl68" style="border-left:none;width:205pt" width="273">88</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:205pt" width="273" height="20"> </td> <td class="xl69" style="border-left:none;width:205pt" width="273">Division 8</td> <td class="xl68" style="border-left:none;width:205pt" width="273">880</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;width:205pt" width="273" height="20"> </td> <td class="xl70" style="border-left:none;width:205pt" width="273">Compulsion Done</td> <td class="xl71" style="border-left:none;width:205pt" width="273"> </td> </tr> </tbody></table>
I would need to delete all 4 rows within the "Cancelled" Box, but in a spreadsheet of 12000 boxes of 4. so, probably a macro to find them all, and delete them would be the way to go. Let me know if you can help!

 

[ChrisOswald]

Hi,

I think this macro should do what you want: see the comments for the assumptions I've made.

Definitely test this on a COPY of your worksheet (no ctrl-z available after running a macro)

Sub DeleteFourRowsifCancelled()
    'NOTE: throughout this macro, I'm assuming that the data containing the word
    ' "Cancelled" is always the 3rd item of 4 in a group (and is in column A)
    ', and the groups always contain 4 rows.  It this is invalid, will need
    'to do this differently
 
    Dim ws As Worksheet
    Dim lr As Long
    Dim i As Long
    Dim j As Long
 
 
    'technically, you don't need to set a worksheet reference for this
    'type of macro: I just always do this so I don't forget to when
    'I need to do this.
    Set ws = ActiveSheet
    'using column B for last row function: Columns A & C appear to not reach to bottome of sheet
    lr = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
 
    'when deleting rows, it's best to work from the bottom up
    For i = lr To 2 Step -1
        'since each chunk of 4 items has 'CANCELLED' in the third row, and we're working
        'bottom up, look at the item one row above.
 
        'converting cell content to uppercase to avoid and problems comparing
        'strings with differing cases
        If UCase(ws.Cells(i - 1, 1)) = "CANCELLED" Then
            ws.Rows(i - 3 & ":" & i).Delete
            'since we deleted some rows, we could 'skip' the counter down
            '4 row numbers (visually up in the sheet), but then we need to worry
            'about trapping out the case where the first chunk of 4 items is in a cancelled
            'state.  Also, messing around with a counter in a For...Next
            'loop is a extrememly efficient way to introduce bugs into a process...
        End If
    Next i
End Sub

 

[SoNaive]

All of your assumptions were true, and this worked perfectly.

Thank you very much!