Macro to retain data

H

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,601
Office Version
  1. 2021
Platform
  1. Windows
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
Hi

Maybe something like:
Code: 
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
 
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Automating a formula using a pivot table and vba
Replies
0
Views
316
K0D54
K
T
  • Question Question
Assistance with Dividing Accommodation Costs by FTE
Replies
3
Views
95
Vogateer
V
N
  • Question Question
Clear data displaying after a Ctrl F 'Find' search
Replies
5
Views
313
Alex Blakenburg
A
V
  • Question Question
Help Needed for a Macro to Transpose
Replies
6
Views
340
alansidman
A
M
  • Solved
Automate insertion of row data
2
Replies
12
Views
583
myall_blues
myall_blues

Forum statistics

Threads
1,224,600
Messages
6,179,836
Members
452,947
Latest member
Gerry_F

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top