Macro to delete unwanted data

[howard]

I would like to macro to delete all data from Row 3 onwards except the rows where the text "Current Closing Balance" appears in Col C as well as 2 rows above this

For Eg if CURRENT CLOSING BALANCE is in row 47, then Rows 45-47 must remain intact

Your assistance will be most appreciated

 

[JamesW]

Hi,

Try:

Sub test()
    lRow = Range("C" & Rows.Count).End(xlUp).Row
    For i = lRow To 3 Step -1
        If Cells(i, 3).Value = "Current Closing Balance" Then
            i = i - 2
        Else
            Cells(i, 3).EntireRow.Delete
        End If
    Next
End Sub

 

[howard]

Hi James

Thanks for the help.

The macro is curently clearing all the data

I need all rows from row 11 containing "CURRENT CLOSING BALANCE" (Col C) + 2 rows above it to remain intact (i.e the first 10 rows to be ignored)

I also need the rows to be deleted where the text appears "Acc Depr" in col C for eg Acc Depr Corporate signage. The the text will allways start with Acc Depr

Your assistance is most appreciated

Fixed Assets.xls
	A	B	C	D	E	F	G
1
2	Company:12Date:09/05/2011
3
4	CurrentAccountingMonth:201105GENERALLEDGERTRANSACTIONLISTINGFORPERIOD:MONTH201103-201104Page:1
5
6	------------------------------------------------------------------------------------------------------------------------------------
7		ACCNO	---------DESCRIPTION-------	TP	Src	Audit	Posted
8			Date	Ref.No.	Control		Cd
9		----------------------------------------------------------------------------------------------------------------------------------
10		----------------------------------------------------------------------------------------------------------------------------------
11		101210	CORPSIGNAGEATCOST	TOTALTRANSACTIONSFORTHEPERIOD	0	0
12		----------------------------------------------------------------------------------------------------------------------------------
13			CURRENTCLOSINGBALANCE	693818.53
14		----------------------------------------------------------------------------------------------------------------------------------
15		101211	ACCDEPRCORPSIGN	L			OpeningBalanceforAcc.Month201103
16
17			11/04/11	69287	69287	J	GJ
18
19							TotalAfterAccountingMonth201103
20		----------------------------------------------------------------------------------------------------------------------------------
21		----------------------------------------------------------------------------------------------------------------------------------
22		101211	ACCDEPRCORPSIGN	TOTALTRANSACTIONSFORTHEPERIOD	0	-11189.4
23		----------------------------------------------------------------------------------------------------------------------------------
24			CURRENTCLOSINGBALANCE		-593985
25		----------------------------------------------------------------------------------------------------------------------------------
Sheet1

The data looks liks this after running the macro

Fixed Assets.xls
	A	B	C	D	E	F	G
1
2	Company:12Date:09/05/2011
3
4	CurrentAccountingMonth:201105GENERALLEDGERTRANSACTIONLISTINGFORPERIOD:MONTH201103-201104Page:1
5
6	------------------------------------------------------------------------------------------------------------------------------------
7	------------------------------------------------------------------------------------------------------------------------------------
8
9	-----------------------------------------------------------------------------------------------------------------------------------
10	GENERALLEDGERTRANSACTIONLISTINGPage:1
Sheet1

The file must look more or less less this once the macro has run

Fixed Assets.xls
	A	B	C	D	E	F
1	RunBy:HOWARDTime:12:21:02
2	GLP520Company:10NissanEasternCapeDate:09/05/2011
3
4	CurrentAccountingMonth:201105GENERALLEDGERTRANSACTIONLISTINGFORPERIOD:MONTH201103-201104Page:1
5
6	------------------------------------------------------------------------------------------------------------------------------------
7		ACCNO	---------DESCRIPTION-------	TP	Src	Audit
8			Date	Ref.No.	Control
9		----------------------------------------------------------------------------------------------------------------------------------
10		----------------------------------------------------------------------------------------------------------------------------------
11		101210	CORPSIGNAGEATCOST	TOTALTRANSACTIONSFORTHEPERIOD	0	0
12		----------------------------------------------------------------------------------------------------------------------------------
13			CURRENTCLOSINGBALANCE	693818.53
14		----------------------------------------------------------------------------------------------------------------------------------
15		101211	ACCDEPRCORPSIGN	TOTALTRANSACTIONSFORTHEPERIOD	0	-11189.38
16		----------------------------------------------------------------------------------------------------------------------------------
17			CURRENTCLOSINGBALANCE		-593984.64
18		101212	COMPEQUIPATCOST	TOTALTRANSACTIONSFORTHEPERIOD	0	0
19		----------------------------------------------------------------------------------------------------------------------------------
20			CURRENTCLOSINGBALANCE	0
21		----------------------------------------------------------------------------------------------------------------------------------
22		----------------------------------------------------------------------------------------------------------------------------------
23		101213	ACCDEPRCOMPEQUIP	TOTALTRANSACTIONSFORTHEPERIOD	0	0
24		----------------------------------------------------------------------------------------------------------------------------------
25			CURRENTCLOSINGBALANCE	0
26		----------------------------------------------------------------------------------------------------------------------------------
27		101214	PLANT&MACHATCOST	TOTALTRANSACTIONSFORTHEPERIOD	0.01	-46930.01
28		----------------------------------------------------------------------------------------------------------------------------------
29			CURRENTCLOSINGBALANCE	464880.9
30
31
Sheet1