Macro to retain specific Data

[howard]

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

 

[howard]

Hi Peter

The worksheet would now look like this after the revised conditions

Nissan Fixed Assets.xls
	A	B	C	D	E	F
1	RunBy:HOWARDTime:07:52:06
2
3
4	CurrentAccountingMonth:201105GENERALLEDGERTRANSACTIONLISTINGFORPERIOD:MONTH201104
5
6
7		ACCNO
8			Date	Ref.No.	Control
9
10
11		101202	GENERATORATCOST	TOTALTRANSACTIONSFORTHEPERIOD	0	0
12
13			CURRENTCLOSINGBALANCE	168546.53
14
15		101206	FURN&FITATCOST	TOTALTRANSACTIONSFORTHEPERIOD	2	0
16
17			CURRENTCLOSINGBALANCE	865867.21
18
19
20		101208	OFFICEEQUIPATCOST	TOTALTRANSACTIONSFORTHEPERIOD	1	1
21
22			CURRENTCLOSINGBALANCE	265363.31
23
24
25		101210	CORPSIGNAGEATCOST	TOTALTRANSACTIONSFORTHEPERIOD	0	0
26
27			CURRENTCLOSINGBALANCE	693818.53
28
29
30		101212	COMPEQUIPATCOST	TOTALTRANSACTIONSFORTHEPERIOD	0	0
31
32			CURRENTCLOSINGBALANCE	0
33
34
35		101214	PLANT&MACHATCOST	TOTALTRANSACTIONSFORTHEPERIOD	0	0
36
37			CURRENTCLOSINGBALANCE	464880.9
38
39
40
41
42
43
Sheet1

 

[Peter_SSs]

The worksheet would now look like this after the revised conditions

Problem is, I asked specifically for what Sheet2 from post #3 would look like.

This data seems to be unrelated to the data posted in post #3 (where the first 'Cost' cell was "CORP SIGNAGE AT COST") so, not knowing what the original data for this sheet looked like, this provides no clarification at all.

 

[howard]

Hi Peter

My Apologies for the confusion

Please see sample data below what sheet#2 should 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:61px;" /><col style="width:61px;" /><col style="width:187px;" /><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 >*</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 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; " >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 > * * * * * * * * * * * * * * * * * * * * * * * * * * * *</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; " >12</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; " >13</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; " >14</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; " >15</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; " >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; ">464880.9</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></table> <br />Excel tables to the web - Excel Jeanie Html 4



If you need the workbook showing Sheet#1 & sheet#2 , I can email this to you

Regards

Howard

 

[Richard Schollar]

This looks like a continuation of your thread from yesterday:

http://www.mrexcel.com/forum/showthread.php?t=548800

I won't lock this post on this occassion as Peter has been assisting you, but please DO NOT POST DUPLICATE POSTS as they will be removed and continued violation of this rule may result in removal of your posting privileges.

Thank you for your cooperation.

 

[howard]

Hi Richard

My apologies. This won't happen again

Regards

Howard

 

[Peter_SSs]

Test this in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> RemoveUnwantedData()<br>    <SPAN style="color:#00007F">Dim</SPAN> r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, br <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> fr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 9<br>    <SPAN style="color:#00007F">Const</SPAN> StTxt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "Cost"<br>    <SPAN style="color:#00007F">Const</SPAN> EndTxt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "Current Closing Balance"<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    r = Range("C" & Rows.Count).End(xlUp).Row<br>    br = r<br>    <SPAN style="color:#00007F">Do</SPAN><br>        s = Cells(r, "C").Value<br>        <SPAN style="color:#00007F">If</SPAN> InStr(1, s, EndTxt, 1) > 0 <SPAN style="color:#00007F">Then</SPAN><br>            s = Cells(r - 2, "C").Value<br>            <SPAN style="color:#00007F">If</SPAN> InStr(1, s, StTxt, 1) > 0 <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">If</SPAN> br > r <SPAN style="color:#00007F">Then</SPAN><br>                    Rows(r + 1 & ":" & br).Delete<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                r = r - 2<br>                br = r - 1<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        r = r - 1<br>    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> r >= fr<br>    <SPAN style="color:#00007F">If</SPAN> br >= fr <SPAN style="color:#00007F">Then</SPAN><br>        Rows(fr & ":" & br).Delete<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[howard]

Hi Peter

Thanks for all the help. This is much appreciated

Code works erfectly

Regards

Howard