Code for Sub-Total command with rows changing in Macro


New Member
Dec 18, 2013
Dear Sir,

I am trying to have a code (to be written for macro) for performing Sub-Totals in the column I excel sheet as a part of automation. The rows will be dynamic and No. of rows for each sub-total changes every time. The sub-total shall be based on certain criteria which will be in other columns. Manually we are filtering for blanks on B column and E column and inserting sub-total formula in I column for sum operation. The total no. of rows may be upto 5000 Lines or more and may be even as less as 100 rows. No. of sub-totals may also vary depending on the rows. Once the macro is run, it shall all perform all the sub-totals based on above explained criteria.

Can any one help.

Pls. see below small example.

Slno.SubItem Description UOMGross Qty Changes in PricesFinal Amount
BU111811947502HV00000Main Item:BU111811947502HV00000 INSULATION ON HV COIL
BU111811947502HV00000 107140400100P.B. GRADE-KPC:1x2000x3000mm kg 10.6337185.001851967.23
BU111811947502HV00000 107140000150P.B. 1.50 mm GRADE-KPC (IB-56 kg 24.2116.70116.72824.14
BU111811947502HV00000 107140400200P.B. GRADE-KPC:2x2000x3000mm kg 6.6185.001851221.00
BU111811947502HV00000 107140000300P.B. 3.00 mm GRADE-KPC (IB-56 kg 9.9116.70116.71155.33
BU111811947502HV00000 107140000400P.B. 4.00 mm GRADE-KPC (IB-56 kg 2.9334122.84122.84360.34
BU111811947502HV00000 107140000500P.B. 5.00 mm GRADE-KPC (IB-56 kg 2.5663122.84122.84315.24
BU111811947502HV00000 107140000600P.B. 6.00 mm GRADE-KPC(IB-56) kg 5.5122.84122.84675.62
BU111811947502HV00000 107100000080P.B 0.80 mm GRADE-3 kg 0.7337220.42220.42161.72
BU111811947502HV00000 BU1118119207140400150P.B.GRADE-KPC:1.5X2000X3000mm kg 1.65116.70116.7192.56
BU111811947502HV00000 107140400300P.B. GRADE-KPC:3x2000x3000mm kg 22185.001854070.00
BU111811947502HV00000 BU1118119207020100001HV IND RING : 709/581 , 35thknos12323.832323.83292323.83
BU111811947502HV00000 BU1118119207020100002HV IND.RING 2:709/581;35THK Nos12323.832323.83292323.83
BU111811947502HV00000 17590.85
BU111811947504CA00000Main Item:BU111811947504CA00000 INSULATION AT CORE ASSEMBLY
BU111811947504CA00000 107140000400P.B. 4.00 mm GRADE-KPC (IB-56 kg 15.18122.84122.841864.71

Thanks in advance.


D Sampath Kumar


Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...