Macro to retain specific Data

H

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,601
Office Version
  1. 2021
Platform
  1. Windows
I have a spreadsheet where I want to retain the following data from row 9 onwards using a macro

1) Where the text cost appears in col C this must me retained up until "CURRENT CLOSING BALANCE" in Col C

2) All other rows to be deleted

I have attached sample data

a) Sheet1 contains the raw data
b) sheet2 shows what the data should look like after running the macro

<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 >XXX</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 >XXX</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td >XXX</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 >XXX</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 >XXX</td><td >XXX</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 >XXX</td><td >XXX</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >XXX</td><td >----------------------------------------------------------------------------------------------------------------------------------</td><td > </td><td > </td><td >XXX</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td >XXX</td><td >XXX</td><td > </td><td > </td><td >XXX</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >XXX</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 >XXX</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 >XXX</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 >XXX</td><td >XXX</td><td >    </td><td >XXX</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td >XXX</td><td >XXX</td><td >  </td><td >XXX</td><td >XXX</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >XXX</td><td >      </td><td style="text-align:right; "> 11/04/11</td><td style="text-align:right; ">69287</td><td style="text-align:right; ">XXX</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 >XXX</td><td >                               </td><td >  </td><td >    </td><td >XXX</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td > </td><td >XXX</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 >XXX</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 >XXX</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 >XXX</td><td > </td><td >XXX</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 >XXX</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td >XXX</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 >XXX</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 >XXX</td><td >                                                        </td><td > CURRENT CLOSING BALANCE                  </td><td style="text-align:right; ">0</td><td >XXX</td><td >XXX</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 >XXX</td><td style="text-align:right; ">101213</td><td > ACC DEPR COMP EQUIP                             </td><td >XXX</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 >XXX</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >34</td><td > </td><td >XXX</td><td > CURRENT CLOSING BALANCE                  </td><td style="text-align:right; ">XXX</td><td >              </td><td >XXX</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 >XXX</td><td >XXX</td><td >XXX</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; ">XXX</td><td >XXX</td><td > A</td><td >XXX</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 >XXX</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 >XXX</td><td style="text-align:right; "> 11/04/11</td><td style="text-align:right; ">69286</td><td style="text-align:right; ">XXX</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 >XXX</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 >XXX</td><td >      </td><td >XXX</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 >XXX</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 >XXX</td><td > </td><td >XXX</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >45</td><td >XXX</td><td style="text-align:right; ">XXX</td><td > PLANT & MACH AT COST                            </td><td >XXX</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 >XXX</td><td style="text-align:right; ">464880.9</td><td >              </td><td >XXX</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 >XXX</td><td >XXX</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >49</td><td > </td><td >XXX</td><td >                                          </td><td >XXX</td><td >XXX</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 >XXX</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 >XXX</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >53</td><td >XXX</td><td > </td><td > </td><td >XXX</td><td > </td><td >XXX</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >54</td><td >-----------------------------------------------------------------------------------------------------------------------------------</td><td >XXX</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 >XXX</td><td > </td><td >XXX</td><td > </td><td > </td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

<b>Sheet2</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:125px;" /><col style="width:64px;" /><col style="width:191px;" /><col style="width:252px;" /><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 >XXX</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 >XXX</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 >XXX</td><td >XXX</td><td >XXX</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 >XXX</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >XXX</td><td >ACC NO</td><td > --------- DESCRIPTION ------- </td><td >TP</td><td >XXX</td><td >XXX</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td >XXX</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 style="text-align:right; ">101210</td><td > CORP SIGNAGE AT COST                            </td><td >XXX</td><td style="text-align:right; ">XXX</td><td style="text-align:right; ">0</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 >XXX</td><td >XXX</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td >XXX</td><td >XXX</td><td style="text-align:right; ">693818.53</td><td >              </td><td >XXX</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >XXX</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; ">XXX</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td >----------------------------------------------------------------------------------------------------------------------------------</td><td >XXX</td><td > </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 >XXX</td><td style="text-align:right; ">0</td><td >              </td><td >XXX</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >XXX</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; " >16</td><td > </td><td >XXX</td><td >                               </td><td >XXX</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; "> 28/03/11</td><td style="text-align:right; ">67404</td><td >           </td><td >XXX</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td > </td><td >XXX</td><td style="text-align:right; "> 11/04/11</td><td style="text-align:right; ">XXX</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; " >19</td><td > </td><td >      </td><td style="text-align:right; ">XXX</td><td style="text-align:right; ">69286</td><td style="text-align:right; ">69286</td><td >XXX</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td >XXX</td><td >      </td><td >                               </td><td >  </td><td >    </td><td >XXX</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td > </td><td >XXX</td><td >                               </td><td >  </td><td >XXX</td><td >         </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td >XXX</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; " >23</td><td > </td><td >XXX</td><td > </td><td > </td><td >XXX</td><td >XXX</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td > </td><td style="text-align:right; ">XXX</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; " >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 > CURRENT CLOSING BALANCE                  </td><td style="text-align:right; ">XXX</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 > </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; " >28</td><td > </td><td > </td><td > </td><td >XXX</td><td > </td><td > </td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
I can see little resemblance between your description and your sample screen shots.

In addition, the screen shots are hard to follow because of their size. Any chance of a smaller set of dummy sample data and expected results without those cells with so many "---" in them?
 
Upvote 0
Hi Peter

Please find raw Data -sheet 1 and what data must look like after running macro

As can be seen after running the macro , the decription containing cost as well as the items below pertaining to the cost in col C up to the closing balance to be retained

All other rows to be deleted

Hope this is clearer

Your assistance will be most appreciated

A) Raw Data
<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:43px;" /><col style="width:64px;" /><col style="width:196px;" /><col style="width:123px;" /></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></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></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

B) What the data must look like after running the macro

<b>Sheet2</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:73px;" /><col style="width:104px;" /><col style="width:159px;" /><col style="width:104px;" /><col style="width:104px;" /><col style="width:104px;" /></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; ">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; " >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 > 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; " >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 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; " >22</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; " >23</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; " >24</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; " >25</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; " >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 > * * *</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; " >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 >*</td><td >*</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 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.01</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 > * * * * * * * * * * * * * * * * * * * * * * * * * * * *</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; " >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 >*</td><td >*</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 >*</td><td >*</td><td >*</td><td >*</td><td >*</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4
 
Upvote 0
Is it possible that a 'Cost' row immediately follows a 'Current Closing Balance' row (that is, the very next row)?
 
Upvote 0
Hi Peter

Thanks for the reply. I is possible for 'Cost' row to immediately follow a 'Current Closing Balance' row

Regards

Howard
 
Upvote 0
Given that the 'Cost' rows and the 'Closing Balance' rows do not come is sequence I'm not finding an easy way to do this.

For example
1. Rows 13 and 24 are 'Closing' rows but there is no 'Cost' row between them.

2. Rows 36 and 45 are 'Cost' rows but there is no 'Closing' row between them.
 
Upvote 0
Hi Peter

Thanks for trying anyway. your assitance is allways appreciated There is not much data, So I will manually delete the unwanted data each month

Regards

Howard
 
Upvote 0
Hi Peter

I have had a relook at the worksheet since posting my reply. I only need the row where cost appears and the "Closing balance that appears below. All the other rowws where the cost appears but no closing balance directly below it can be deleted as well as any other rows not containing "cost" in Col C as well as "closing Balance" that is 2 rows below the Cost

It would be appreciated if you would write code to do this

Thanks

Howard
 
Upvote 0
howard said:
Hi Peter

I have had a relook at the worksheet since posting my reply. I only need the row where cost appears and the "Closing balance that appears below. All the other rowws where the cost appears but no closing balance directly below it can be deleted as well as any other rows not containing "cost" in Col C as well as "closing Balance" that is 2 rows below the Cost

It would be appreciated if you would write code to do this

Thanks

Howard
Click to expand...
So what would Sheet2 from post #3 look like with these revised conditions?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
105
flashgordie
F
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
E
  • Question Question
Adding a custom message.
Replies
1
Views
666
Micron
M
k3yn0t3
  • Solved
INDEX(MATCH()) pulling wrong row data & Macro to duplicate sheets with formulas
Replies
4
Views
570
Alex Blakenburg
A

Forum statistics

Threads
1,224,599
Messages
6,179,827
Members
452,946
Latest member
JoseDavid

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