Macro to clear data where value sums to zero

[howard]

I have values in Col b and percentages in Col G


Where the % is the same in Col G for eg 1% and the values in Col b for that % sums to zero , then the data in Col A:G to be cleared from row 3 onwards

In the sample data below A2:G3 to be cleared as the % is the same and the values in Col B for 1% sums to zero


It would be appreciated I someone can write code to do this for me


Your assistance in this regard is most appreciated

Book1
	A	B	C	D	E	F	G	H
1	Ref	Vat	Value				Percentage
2	f9152	18.42	3308.92				1.00%
3	f9150	-18.42	-3327.34				1.00%
4	F9589	-1052.11	-3515.1				5.00%
5	F9590	-1793.81	-3012.93				6.00%
6
7
8
9
10
11
12
13
Sheet1

 

[Special-K99]

Am no VBA expert (so take a backup and check the results thoroughly,) but this works,

Assumes the following:
that by "clear" you mean clear the rows and not delete them
that you are on that sheet when you run the code
that the rows that need to be cleared are below each other as per your example
that only TWO rows need to be cleared each time, as per your example

Sub ClrPercent()
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow
If Cells(i, 7) = Cells(i + 1, 7) And Cells(i, 2) + Cells(i + 1, 2) = 0 Then
Range(Cells(i, 1), Cells(i + 1, 7)).Clear
End If
Next i
End Sub

 

[howard]

Thanks for the help. It works perfectly

 

[howard]

I would like the 0 to be amended so that where the value I B sum to zero or up to a maximum of 0.00002 or -0.00002 then the rows that adds up to this value also to be cleared


your assistance is most appreciated

 

[Special-K99]

Change the line to

If Cells(i, 7) = Cells(i + 1, 7) And Abs(Cells(i, 2) + Cells(i + 1, 2)) <= 0.00002 Then

 

[howard]

thanks for the help. I have tested the data further and where the % is the same in Col G and the values for a particular % in Col b adds up to zero are there is a small value after the decimal place for eg 0.00001, 0.000002 etc these must be cleared


See further example blow

Book1
	A	B	C	D	E	F	G
1	F5166	-1532.9					15.00%
2	F5166	-2279.96					15.00%
3	9255280	-42.17					15.00%
4	NLK-920654	42.17					15.00%
5	JLP70569	1532.9					15.00%
6	916817PL	2279.96					15.00%
Sheet1

 

[Special-K99]

That should be covered by the recent change since
0.000002 is less than 0.00002

If the value is not clearing then the %s in column G are not the same (maybe 14.9999% instead of 15%?)

Unfortunately I dont have any more time to spend on this as I am at work and am only able to take on small Excel problems in the spare time I have.

 

[howard]

I do appreciate your help, and fully understand your dilemma