VBA to sum columns in blank cells

K

karadeon

New Member
Joined
Oct 25, 2016
Messages
3
Hello!

I'm trying to sum certain columns and for the sum to be placed inside the blanks while adding a row after the sum. I used a macro to fill in the Deposits column (which is column D) and the Mkt Share column (which is column E). However, I would need it to also sum column "Br" (which is column F), "Pen" (which is column G). And I would need a blank row to appear after the sum row. I'll place my code in here.

Thanks for your help!

Sub Subtotal()
Dim lngLastRow As Long, _
lngFormulaRowStart As Long, _
lngFormulaRowEnd As Long
Dim rngCell As Range

lngLastRow = Cells(Rows.Count, "D").End(x1Up).Row + 1
lngFormulaRowStart = 2
lngFormulaRowEnd = 2

Application.ScreenUpdating = False

For Each rngCell In Range("D2:D" & lngLastRow)

If Len(rngCell.Value) = 0 Then

lngFormulaRowEnd = rngCell.Row - 1

rngCell.Formula = "=SUM(D" & lngFormulaRowStart & ":D" & lngFormulaRowEnd & ")"
rngCell.Offset(0, 1).Formula = "=SUM(E" & lngFormulaRowStart & ":E" & lngFormulaRowEnd & ")"

lngFormulaRowStart = rngCell.Offset(1, 0).Row
lngFormulaRowEnd = rngCell.Offset(1, 0).Row

End If

Next rngCell

Application.ScreenUpdating = True

End Sub


[TABLE="width: 593"]
<colgroup><col width="197" style="width: 148pt; mso-width-source: userset; mso-width-alt: 7204;"> <col width="285" style="width: 214pt; mso-width-source: userset; mso-width-alt: 10422;"> <col width="36" style="width: 27pt; mso-width-source: userset; mso-width-alt: 1316;"> <col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;"> <col width="49" style="width: 37pt; mso-width-source: userset; mso-width-alt: 1792;"> <col width="28" style="width: 21pt; mso-width-source: userset; mso-width-alt: 1024;"> <col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;"> <tbody>[TR]
[TD="class: xl66, width: 197, bgcolor: #99CC00"]CBSA[/TD]
[TD="class: xl66, width: 285, bgcolor: #99CC00"]Bank[/TD]
[TD="class: xl67, width: 36, bgcolor: #99CC00"]Rank[/TD]
[TD="class: xl68, width: 76, bgcolor: #99CC00"]Deposits[/TD]
[TD="class: xl69, width: 49, bgcolor: #99CC00"]Mkt Shr[/TD]
[TD="class: xl68, width: 28, bgcolor: #99CC00"]Br[/TD]
[TD="class: xl69, width: 42, bgcolor: #99CC00"]Pen[/TD]
[TD="class: xl68, width: 76, bgcolor: #99CC00"]Dep/Br[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Aberdeen, SD[/TD]
[TD="class: xl63, bgcolor: transparent"]Wells Fargo & Company (CA)[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]361,940[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]24.4%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13.0%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]120,647[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Aberdeen, SD[/TD]
[TD="class: xl63, bgcolor: transparent"]Dacotah Banks, Inc. (SD)[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]339,165[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]22.8%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8.7%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]169,583[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Aberdeen, SD[/TD]
[TD="class: xl63, bgcolor: transparent"]Great Western Bancorp, Inc. (SD)[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]172,814[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11.6%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8.7%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]86,407[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Aberdeen, SD[/TD]
[TD="class: xl63, bgcolor: transparent"]Plains Commerce Bank (SD)[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]157,716[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10.6%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4.3%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]157,716[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Aberdeen, SD[/TD]
[TD="class: xl63, bgcolor: transparent"]U.S. Bancorp (MN)[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]129,930[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8.7%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4.3%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]129,930[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Aberdeen, SD[/TD]
[TD="class: xl63, bgcolor: transparent"]Roscoe Community Bankshares, Inc. (SD)[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]84,414[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5.7%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8.7%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]42,207[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Aberdeen, SD[/TD]
[TD="class: xl63, bgcolor: transparent"]First Bank Shares Corporation (SD)[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]52,950[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3.6%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8.7%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]26,475[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Aberdeen, SD[/TD]
[TD="class: xl63, bgcolor: transparent"]Ipswich Community Bancshares, Inc. (SD)[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]45,064[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3.0%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4.3%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]45,064[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Aberdeen, SD[/TD]
[TD="class: xl63, bgcolor: transparent"]First State Bank of Claremont (SD)[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]35,983[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2.4%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13.0%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]11,994[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Aberdeen, SD[/TD]
[TD="class: xl63, bgcolor: transparent"]Hopkins Financial Corporation (SD)[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]31,701[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2.1%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8.7%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]15,851[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Aberdeen, SD[/TD]
[TD="class: xl63, bgcolor: transparent"]Great Plains Bank Corporation (SD)[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]30,451[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2.0%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4.3%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]30,451[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Aberdeen, SD[/TD]
[TD="class: xl63, bgcolor: transparent"]First National Bank of Frederick (SD)[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]17,819[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1.2%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4.3%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]17,819[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Aberdeen, SD[/TD]
[TD="class: xl63, bgcolor: transparent"]H2H Bancshares, Inc. (SD)[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]15,526[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1.0%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4.3%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]15,526[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Aberdeen, SD[/TD]
[TD="class: xl63, bgcolor: transparent"]Beresford Bancorporation, Inc. (SD)[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]10,506[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.7%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4.3%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]10,506[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1,485,979[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]100.0%[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Aberdeen, WA[/TD]
[TD="class: xl63, bgcolor: transparent"]Pacific Financial Corporation (WA)[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]307,474[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]33.4%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]21.7%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]61,495[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Aberdeen, WA[/TD]
[TD="class: xl63, bgcolor: transparent"]Timberland Bancorp, Inc. (WA)[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]218,234[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]23.7%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]26.1%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]36,372[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Aberdeen, WA[/TD]
[TD="class: xl63, bgcolor: transparent"]Anchor Bancorp (WA)[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]206,686[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]22.5%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]21.7%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]41,337[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Aberdeen, WA[/TD]
[TD="class: xl63, bgcolor: transparent"]Umpqua Holdings Corporation (OR)[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]83,564[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9.1%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8.7%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]41,782[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Aberdeen, WA[/TD]
[TD="class: xl63, bgcolor: transparent"]U.S. Bancorp (MN)[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]30,361[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3.3%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4.3%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]30,361[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Aberdeen, WA[/TD]
[TD="class: xl63, bgcolor: transparent"]KeyCorp (OH)[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]27,395[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3.0%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4.3%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]27,395[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Aberdeen, WA[/TD]
[TD="class: xl63, bgcolor: transparent"]JPMorgan Chase & Co. (NY)[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]21,795[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2.4%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4.3%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]21,795[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Aberdeen, WA[/TD]
[TD="class: xl63, bgcolor: transparent"]Cascade Bancorp (OR)[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]15,923[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1.7%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4.3%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]15,923[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Aberdeen, WA[/TD]
[TD="class: xl63, bgcolor: transparent"]Security State Corporation (WA)[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]9,042[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1.0%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4.3%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]9,042[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]920,474[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]100.0%[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Abilene, TX[/TD]
[TD="class: xl63, bgcolor: transparent"]First Financial Bankshares, Inc. (TX)[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1,219,536[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]46.0%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]29.5%[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]93,810[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Hi,

I am not 100% sure I know what your requirements are because you are asking for subtotals and you already have some listed. Anyway this code will subtotal columns "F" & "G" and then insert a blank row.

See if it comes close to what you are looking for... Please test on a backup copy of your data.

Code: 
Sub GetSubs()


' Add Subtotals
    Application.ScreenUpdating = False
    Dim subt As Double, subt2 As Double
    Dim break As Long
    Dim lastrow As Long


    lastrow = Cells(Rows.Count, "F").End(xlUp).Row
    
nxt:
    break = Cells(lastrow, "F").End(xlUp).Row
    If Cells(lastrow - 1, 6) = "" Then break = lastrow
    subt = WorksheetFunction.Sum(Range("F" & break, "F" & lastrow))
    Range("F" & lastrow).Offset(1, 0).Value = subt
    subt2 = WorksheetFunction.Sum(Range("G" & break, "G" & lastrow))
    Range("G" & lastrow).Offset(1, 0).Value = Format(subt2, "Percent")
    Range("F" & lastrow).Offset(1, 0).Offset(1, 0).EntireRow.Insert
    lastrow = Cells(break, 6).End(xlUp).Row
    If lastrow < 2 Then End
    
    GoTo nxt
    Application.ScreenUpdating = True
    
End Sub

I hope this helps!

igold
 
Upvote 0
This was perfect!!!! Thank you so much for your help!!!

Is it possible do delete groups of rows if a certain name doesn't appear in that group? In the picture I pasted in here, it had Aberdeen, SD and Aberdeen WA. Let's say I wanted to keep the groups of cities that has Anchor Bancorp in that city. Anchor Bancorp appears in Aberdeen, WA but doesn't appear in Aberdeen, SD. Is there a way to delete all the lines of Aberdeen, SD because that specific bank isn't in that city?
 
Upvote 0
This was perfect!!!! Thank you so much for your help!!!

Is it possible do delete groups of rows if a certain name doesn't appear in that group? In the picture I pasted in here, it had Aberdeen, SD and Aberdeen WA. Let's say I wanted to keep the groups of cities that has Anchor Bancorp in that city. Anchor Bancorp appears in Aberdeen, WA but doesn't appear in Aberdeen, SD. Is there a way to delete all the lines of Aberdeen, SD because that specific bank isn't in that city?
 
Upvote 0
Hi karadeon,

Thanks for the feedback, I am glad we got that part working.

I almost forgot... Welcome to MrExcel.

See if this fulfills your new requirement. Please be sure to test on a backup copy of your worksheet, as this code will delete data that you cannot recover.

Code: 
Sub GetSubs2()


    'Add Subtotals and remove groups
    Dim bf As Boolean
    Dim subt As Double, subt2 As Double
    Dim break As Long, i As Long, lastrow As Long
    
    Application.ScreenUpdating = False
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    
nextgrp:
    bf = False
    break = Cells(lastrow, "F").End(xlUp).Row
    If break = 1 Then break = 2
    If Cells(lastrow - 1, 6) = "" Then break = lastrow
    For i = break To lastrow
        If Cells(i, 2) = "Anchor Bancorp (WA)" Then
            bf = True
            Exit For
        End If
    Next
    If Not bf Then Range(Cells(break, 1), Cells(lastrow + 1, 8)).Delete
    lastrow = Cells(break, 6).End(xlUp).Row
    If lastrow < 2 Then GoTo nxtsec
    GoTo nextgrp
    
nxtsec:
    lastrow = Cells(Rows.Count, "F").End(xlUp).Row
    
nxt:
    break = Cells(lastrow, "F").End(xlUp).Row
    subt = WorksheetFunction.Sum(Range("F" & break, "F" & lastrow))
    Range("F" & lastrow).Offset(1, 0).Value = subt
    subt2 = WorksheetFunction.Sum(Range("G" & break, "G" & lastrow))
    Range("G" & lastrow).Offset(1, 0).Value = Format(subt2, "Percent")
    Range("F" & lastrow).Offset(1, 0).Offset(1, 0).EntireRow.Insert
    lastrow = Cells(break, 6).End(xlUp).Row
    If lastrow < 2 Then GoTo CleanUp
    GoTo nxt
    
CleanUp:
    Application.ScreenUpdating = True
    
End Sub

Let me know how it goes...

igold
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Conditional formating where there are 2 values in a cell
Replies
3
Views
1K
Godders199
G
H
  • Question Question
Macros - Auto Copy paste row when cell value changes
Replies
7
Views
6K
Fluff
Fluff
A
  • Question Question
VBA Run-time error '1004'
2
Replies
11
Views
2K
JustynaMK
JustynaMK
S
  • Question Question
Conditional Formating Required for changing Dates based on 2 different cell dates
Replies
1
Views
812
Toadstool
Toadstool
L
  • Question Question
VBA - Consolidate data and Sum mulitiple ranges
Replies
1
Views
2K
MickG
M

Share this page

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
Back
Top