Macro to retain data

[howard]

I have a spreadsheet that contains the word "cost" in the text in Col C for eg GENERATOR AT COST

I would like a macro that will retain the row where the word "cost" appears in the text as well as 2 rows below this, starting from row 8 onwards

Your assistance in this regard will be most appreciated

 

[Jon von der Heyden]

Hi

Maybe something like:

Public Sub Demo()
    Dim rngTable As Range
    Dim lngIndex As Long
    Dim rngCheck As Range
    Dim varOffset As Variant
    Const strKey As String = "* COST"
    
    With ActiveSheet
        Set rngTable = Range(.Range("C8"), .Range("C" & .Rows.Count).End(xlUp))
    End With

    For lngIndex = rngTable.Row + 2 To rngTable.Rows.Count + rngTable.Row Step 3
        Set rngCheck = rngTable(lngIndex - rngTable.Row - 1).Resize(3)
        varOffset = Application.Match(strKey, rngCheck, 0)
        If IsNumeric(varOffset) Then
            rngCheck.Offset(varOffset - 1).EntireRow.Hidden = True
        End If
    Next lngIndex
    
    With rngTable
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .EntireRow.Hidden = False
    End With
End Sub

 

[howard]

Hi Jon

My apologies. I need the macro amended as follows:

1) Where the text "Cost" appears in Col C I need this to be retained up where the word "closing balance" appears 2 rows below the word "Cost" in Col C"

2) the first eight Rows i.e row 1 to 8 for Eg A1:D8

3) all the other rows to be deleted

Your assistance will be most appreciated

Regards

Howard

 

[howard]

Hi Jon

Have posted Sample Data to make it easier to see what is required

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:180px;" /><col style="width:282px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Run By: HOWARD                                                                                                     Time: 12:21:02</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Current Accounting Month: 201105               GENERAL LEDGER TRANSACTION LISTING FOR PERIOD: MONTH 201103-201104  Page:        1</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >------------------------------------------------------------------------------------------------------------------------------------</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td >ACC NO</td><td > --------- DESCRIPTION ------- </td><td >TP</td><td > Src</td><td >   Audit </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td >      </td><td >   Date  </td><td > Ref. No.</td><td > Control   </td><td >  </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td >----------------------------------------------------------------------------------------------------------------------------------</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td >----------------------------------------------------------------------------------------------------------------------------------</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td style="text-align:right; ">101210</td><td > CORP SIGNAGE AT COST                            </td><td > TOTAL TRANSACTIONS FOR THE PERIOD        </td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td >----------------------------------------------------------------------------------------------------------------------------------</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td >                                                        </td><td > CURRENT CLOSING BALANCE                  </td><td style="text-align:right; ">693818.53</td><td >              </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td >----------------------------------------------------------------------------------------------------------------------------------</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td style="text-align:right; ">101211</td><td > ACC DEPR CORP SIGN            </td><td > L</td><td >    </td><td >         </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td >      </td><td >                               </td><td >  </td><td >    </td><td >         </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td><td >      </td><td style="text-align:right; "> 11/04/11</td><td style="text-align:right; ">69287</td><td style="text-align:right; ">69287</td><td > J</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td > </td><td >      </td><td >                               </td><td >  </td><td >    </td><td >         </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td > </td><td >      </td><td >                               </td><td >  </td><td >    </td><td >         </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td > </td><td >----------------------------------------------------------------------------------------------------------------------------------</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td > </td><td >----------------------------------------------------------------------------------------------------------------------------------</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td > </td><td style="text-align:right; ">101211</td><td > ACC DEPR CORP SIGN                              </td><td > TOTAL TRANSACTIONS FOR THE PERIOD        </td><td style="text-align:right; ">0</td><td style="text-align:right; ">-11189.4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td > </td><td >----------------------------------------------------------------------------------------------------------------------------------</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td > </td><td >                                                        </td><td > CURRENT CLOSING BALANCE                  </td><td >               </td><td style="text-align:right; ">-593985</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td > </td><td >----------------------------------------------------------------------------------------------------------------------------------</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td > </td><td >----------------------------------------------------------------------------------------------------------------------------------</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td > </td><td style="text-align:right; ">101212</td><td > COMP EQUIP AT COST                              </td><td > TOTAL TRANSACTIONS FOR THE PERIOD        </td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td > </td><td >----------------------------------------------------------------------------------------------------------------------------------</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td > </td><td >                                                        </td><td > CURRENT CLOSING BALANCE                  </td><td style="text-align:right; ">0</td><td >              </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td > </td><td >----------------------------------------------------------------------------------------------------------------------------------</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td > </td><td >----------------------------------------------------------------------------------------------------------------------------------</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >32</td><td > </td><td style="text-align:right; ">101213</td><td > ACC DEPR COMP EQUIP                             </td><td > TOTAL TRANSACTIONS FOR THE PERIOD        </td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >33</td><td > </td><td >----------------------------------------------------------------------------------------------------------------------------------</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >34</td><td > </td><td >                                                        </td><td > CURRENT CLOSING BALANCE                  </td><td style="text-align:right; ">0</td><td >              </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >35</td><td > </td><td >----------------------------------------------------------------------------------------------------------------------------------</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >36</td><td > </td><td style="text-align:right; ">101214</td><td > PLANT & MACH AT COST          </td><td > A</td><td >    </td><td >         </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >37</td><td > </td><td >      </td><td >                               </td><td >  </td><td >    </td><td >         </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >38</td><td > </td><td >      </td><td style="text-align:right; "> 28/03/11</td><td style="text-align:right; ">67404</td><td >           </td><td > J</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >39</td><td > </td><td >      </td><td style="text-align:right; "> 11/04/11</td><td style="text-align:right; ">69286</td><td style="text-align:right; ">67404</td><td > J</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >40</td><td > </td><td >      </td><td style="text-align:right; "> 11/04/11</td><td style="text-align:right; ">69286</td><td style="text-align:right; ">69286</td><td > J</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >41</td><td > </td><td >      </td><td >                               </td><td >  </td><td >    </td><td >         </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >42</td><td > </td><td >      </td><td >                               </td><td >  </td><td >    </td><td >         </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >43</td><td > </td><td >----------------------------------------------------------------------------------------------------------------------------------</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >44</td><td > </td><td >----------------------------------------------------------------------------------------------------------------------------------</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >45</td><td > </td><td style="text-align:right; ">101214</td><td > PLANT & MACH AT COST                            </td><td > TOTAL TRANSACTIONS FOR THE PERIOD        </td><td style="text-align:right; ">0.01</td><td style="text-align:right; ">-46930</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >46</td><td > </td><td >----------------------------------------------------------------------------------------------------------------------------------</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >47</td><td > </td><td >                                                        </td><td > CURRENT CLOSING BALANCE                  </td><td style="text-align:right; ">464880.9</td><td >              </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >48</td><td > </td><td >----------------------------------------------------------------------------------------------------------------------------------</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >49</td><td > </td><td >                                                        </td><td >                                          </td><td >               </td><td >              </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >50</td><td > </td><td >                                                        </td><td >  GRAND TOTALS FOR    3   ACCOUNTS        </td><td style="text-align:right; ">0.01</td><td style="text-align:right; ">-58119.4</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >51</td><td > </td><td >                                                        </td><td >                                          </td><td >               </td><td >              </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >52</td><td >------------------------------------------------------------------------------------------------------------------------------------</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >53</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >54</td><td >-----------------------------------------------------------------------------------------------------------------------------------</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >55</td><td >                                                GENERAL LEDGER TRANSACTION LISTING                                      Page:    1</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4