VBA Sum formula... adjust dynamically

[Mallesh23]

Hi Team,

I want to delete expenses Row where Grand Total is 0.00 In Column D.
But Salary Payble Which is at Row(44). Sum should not get affected.

I am copying sum formula from Mapping sheet, given end user option how to sum it.
after deletion of zero value, formula gets affected.

if Salary payble rows go up after deletion its ok.
Sample expected output is in Range("F:I")

Below is a table with Output.

Book5
	A	B	C	D	E	F	G	H	I
2	Expense head	Cost Unit1	Cost Unit2	Grand Total		Expense head	Cost Unit1	Cost Unit2	Grand Total
3	Basic	30,561.00	7,18,539.00	7,49,100.00		Basic	30,561.00	7,18,539.00	7,49,100.00
4	City Compensatory Allowance	14,668.00	2,58,027.00	2,72,695.00		City Compensatory Allowance	14,668.00	2,58,027.00	2,72,695.00
5	H.R.A.	12,225.00	2,87,416.00	2,99,641.00		H.R.A.	12,225.00	2,87,416.00	2,99,641.00
6	Children Edu Allowance	0.00	110.00	110.00		Children Edu Allowance	0.00	110.00	110.00
7	Car Allowances	0.00	0.00	0.00		Special Allowance	0.00	85,847.00	85,847.00
8	Telelphone Reimbursement	0.00	0.00	0.00		Shift Allowance	0.00	630.00	630.00
9	Driver's Salary	0.00	0.00	0.00
10	Fuel & Maintenance	0.00	0.00	0.00		PF- Employer contribution	3,667.00	86,226.00	89,893.00
11	Special Allowance	0.00	85,847.00	85,847.00		PF Admin EDLI Charges	227.80	4,717.69	4,945.49
12	Bonus accrued (Paid)	0.00	0.00	0.00		Labour Welfare Fund Employer	50.00	750.00	800.00
13	Shift Allowance	0.00	630.00	630.00
14	Relocation Allowance	0.00	0.00	0.00		TDS Payable	0.00	1,37,550.00	1,37,550.00
15	Retention	0.00	0.00	0.00		PF Payable (Employer)	3,894.80	90,943.69	94,838.49
16	Awards	0.00	0.00	0.00		PF Payable (Employee)	3,667.00	86,226.00	89,893.00
17	SAR/RSU Payments	0.00	0.00	0.00		Labour Welfare Fund	75.00	1,125.00	1,200.00
18	Recognition Award	0.00	0.00	0.00
19	Notice Period Payment	0.00	0.00	0.00		Cafetaria Deduction	230.00	2,840.00	3,070.00
20	Other Pay Non Taxable	0.00	0.00	0.00		Transportation Deduction	150.00	2,250.00	2,400.00
21	Leave Encashment	0.00	0.00	0.00
22	Salary Advance Payment	0.00	0.00	0.00		Salary Payable	53,382.00	11,21,328.00	11,74,710.00
23
24	PF- Employer contribution	3,667.00	86,226.00	89,893.00
25	PF Admin EDLI Charges	227.80	4,717.69	4,945.49
26	ESI- Employer contribution	0.00	0.00	0.00
27	Labour Welfare Fund Employer	50.00	750.00	800.00
28
29	TDS Payable	0.00	1,37,550.00	1,37,550.00
30	PF Payable (Employer)	3,894.80	90,943.69	94,838.49
31	PF Payable (Employee)	3,667.00	86,226.00	89,893.00
32	ESI Payable(Employer)	0.00	0.00	0.00
33	ESI Payable(Employee)	0.00	0.00	0.00
34	Labour Welfare Fund	75.00	1,125.00	1,200.00
35
36	Cafetaria Deduction	230.00	2,840.00	3,070.00
37	Transportation Deduction	150.00	2,250.00	2,400.00
38
39	Salary Advance	0.00	0.00	0.00
40	Other Deduction	0.00	0.00	0.00
41	Donation	0.00	0.00	0.00
42	PF Recovery	0.00	0.00	0.00
43
44	Salary Payable	53,382.00	11,21,328.00	11,74,710.00
Sheet1

Cell Formulas
Range	Formula
D44,D39:D42,D36:D37,D29:D34,D24:D27,D3:D22,I19:I20,I14:I17,I10:I12,I3:I8	I3	=SUM(G3:H3)
G15:H15	G15	=+G10+G11
G17:H17	G17	=G12*1.5
B30:C30	B30	=+B24+B25
B34:C34	B34	=B27*1.5
B44:C44	B44	=+SUM(B3:B22)-B29-B31-B33-(B34-B27)-B36-B37-B39-B40-B41-B42

Thanks
mg

 

[JLGWhiz]

I don't believe you really want to delete the row. If you want the cell in column D to show as blank then change your forumula to an IF() statement.
In Cell D3: =IF(SUM(B3:C3>0,SUM(B3:C3),""). Then drag down through Row 42.
That would then make cells in column D appear to be blank when column B and C are blank or zero.

 

[JLGWhiz]

I should have read your post a little more carefully. Try this on a copy of your file first.

Sub t()
Dim i As Long
With ActiveSheet
    For i = 42 To 3 Step -1
        If .Cells(i, 4).HasFormula = True And .Cells(i, 4).Value = 0 Then
            Rows(i).Delete
        End If
    Next
End With
End Sub

 

[Mallesh23]

Hi JLGWhiz
.
Thanks for your help, its working,
one more thing is required I will reframe my question and post.


Thanks
mg

 

[JLGWhiz]

You're welcome,
regards, JLG