VBA Sum formula... adjust dynamically

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
853
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDEFGHI
2Expense headCost Unit1Cost Unit2Grand TotalExpense headCost Unit1Cost Unit2Grand Total
3Basic30,561.007,18,539.007,49,100.00Basic30,561.007,18,539.007,49,100.00
4City Compensatory Allowance14,668.002,58,027.002,72,695.00City Compensatory Allowance14,668.002,58,027.002,72,695.00
5H.R.A.12,225.002,87,416.002,99,641.00H.R.A.12,225.002,87,416.002,99,641.00
6Children Edu Allowance0.00110.00110.00Children Edu Allowance0.00110.00110.00
7Car Allowances0.000.000.00Special Allowance0.0085,847.0085,847.00
8Telelphone Reimbursement0.000.000.00Shift Allowance0.00630.00630.00
9Driver's Salary0.000.000.00
10Fuel & Maintenance0.000.000.00PF- Employer contribution3,667.0086,226.0089,893.00
11Special Allowance0.0085,847.0085,847.00PF Admin EDLI Charges227.804,717.694,945.49
12Bonus accrued (Paid)0.000.000.00Labour Welfare Fund Employer50.00750.00800.00
13Shift Allowance0.00630.00630.00
14Relocation Allowance0.000.000.00TDS Payable0.001,37,550.001,37,550.00
15Retention0.000.000.00PF Payable (Employer)3,894.8090,943.6994,838.49
16Awards0.000.000.00PF Payable (Employee)3,667.0086,226.0089,893.00
17SAR/RSU Payments0.000.000.00Labour Welfare Fund75.001,125.001,200.00
18Recognition Award0.000.000.00
19Notice Period Payment0.000.000.00Cafetaria Deduction230.002,840.003,070.00
20Other Pay Non Taxable0.000.000.00Transportation Deduction150.002,250.002,400.00
21Leave Encashment0.000.000.00
22Salary Advance Payment0.000.000.00Salary Payable53,382.0011,21,328.0011,74,710.00
23
24PF- Employer contribution3,667.0086,226.0089,893.00
25PF Admin EDLI Charges227.804,717.694,945.49
26ESI- Employer contribution0.000.000.00
27Labour Welfare Fund Employer50.00750.00800.00
28
29TDS Payable0.001,37,550.001,37,550.00
30PF Payable (Employer)3,894.8090,943.6994,838.49
31PF Payable (Employee)3,667.0086,226.0089,893.00
32ESI Payable(Employer)0.000.000.00
33ESI Payable(Employee)0.000.000.00
34Labour Welfare Fund75.001,125.001,200.00
35
36Cafetaria Deduction230.002,840.003,070.00
37Transportation Deduction150.002,250.002,400.00
38
39Salary Advance0.000.000.00
40Other Deduction0.000.000.00
41Donation0.000.000.00
42PF Recovery0.000.000.00
43
44Salary Payable53,382.0011,21,328.0011,74,710.00
Sheet1
Cell Formulas
RangeFormula
D44,D39:D42,D36:D37,D29:D34,D24:D27,D3:D22,I19:I20,I14:I17,I10:I12,I3:I8I3=SUM(G3:H3)
G15:H15G15=+G10+G11
G17:H17G17=G12*1.5
B30:C30B30=+B24+B25
B34:C34B34=B27*1.5
B44:C44B44=+SUM(B3:B22)-B29-B31-B33-(B34-B27)-B36-B37-B39-B40-B41-B42


Thanks
mg
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
 
Last edited:

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
I should have read your post a little more carefully. Try this on a copy of your file first.

VBA Code:
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

Well-known Member
Joined
Feb 4, 2009
Messages
853
Office Version
  1. 2010
Platform
  1. Windows
Hi JLGWhiz
.
Thanks for your help, its working,
one more thing is required I will reframe my question and post.


Thanks
mg
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You're welcome,
regards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,127,075
Messages
5,622,533
Members
415,906
Latest member
MrBoomer2U

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top