Macro to retain specific Data

howard · May 9, 2011

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

Sheet1 <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> Excel tables to the web - Excel Jeanie Html 4

Sheet2 <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> Excel tables to the web - Excel Jeanie Html 4

howard · May 10, 2011

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

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

21

22

CURRENTCLOSINGBALANCE

265363.31

23

24

25

101210

CORPSIGNAGEATCOST

TOTALTRANSACTIONSFORTHEPERIOD

0

26

27

CURRENTCLOSINGBALANCE

693818.53

28

29

30

101212

COMPEQUIPATCOST

TOTALTRANSACTIONSFORTHEPERIOD

0

31

32

CURRENTCLOSINGBALANCE

0

33

34

35

101214

PLANT&MACHATCOST

TOTALTRANSACTIONSFORTHEPERIOD

0

36

37

CURRENTCLOSINGBALANCE

464880.9

38

39

40

41

42

43

Sheet1

Peter_SSs · May 10, 2011

howard said:
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 · May 10, 2011

Hi Peter

My Apologies for the confusion

Please see sample data below what sheet#2 should look like after running the macro

Sheet2 <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> 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 · May 10, 2011

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 · May 10, 2011

Hi Richard

My apologies. This won't happen again

Regards

Howard

Peter_SSs · May 11, 2011

Test this in a copy of your workbook.

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

howard · May 11, 2011

Hi Peter

Thanks for all the help. This is much appreciated

Code works erfectly

Regards

Howard

Macro to retain specific Data

howard

Well-known Member

howard

Well-known Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

howard

Well-known Member

Richard Schollar

MrExcel MVP

howard

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

howard

Well-known Member

Similar threads

Forum statistics

Share this page

Macro to retain specific Data

Well-known Member

Well-known Member

Excel Facts

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP, Moderator

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock