Macro Add row

D

dennisli

Well-known Member
Joined
Feb 20, 2004
Messages
1,070
Good afternoon,
In the attached table, is possible to use Macro to add one empty row after the row which includes “Total” and compute the sum function for Grand Total which is equal to the sum of rows containing “Total”?
Thanks.

BI 2001 123 81 340205.38
COLL 2001 2
COMP 2001 1
PD 2001 304 4 3901
PIP 2001 154 43 33098.45
UM 2001 46 2 4000
Totals 630 130 381204.83
BI 2002 345 249 867863.56
COLL 2002 23
COMP 2002 3
PD 2002 799 27 37800
PIP 2002 482 273 274405.93
UM 2002 77 32 64500
Totals 1729 581 1244569.49
BI 2003 339 323 856015.39
COLL 2003 46
COMP 2003 8
PD 2003 1074 352 507179.61
PIP 2003 607 572 836501.82
UM 2003 81 77 154000
Totals 2155 1324 2353696.82
Grand Total
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
You may want to consider deleting the "Totals" rows and using Subtotals under the Data menu. Easily summarizing data is built into Excel already without using macros.

Hope this helps!
 
Upvote 0
Here's what I got when I used Subtotals on your data:
Book1
ABCDEF
1PersonYearNum1Num2Num3
2BI200112381340205.38
3COLL20012
4COMP20011
5PD200130443901
6PIP20011544333098.45
7UM20014624000
82001Total630130381204.83
9BI2002345249867863.56
10COLL200223
11COMP20023
12PD20027992737800
13PIP2002482273274405.93
14UM2002773264500
152002Total17295811244569.49
16BI2003339323856015.39
17COLL200346
18COMP20038
19PD20031074352507179.61
20PIP2003607572836501.82
21UM20038177154000
222003Total215513242353696.82
23GrandTotal451420353979471.14
24
Sheet1


This is after deleting the Totals rows.
 
Upvote 0
Thanks, Kristy,
I use the sumif function to get the Total and Grand Total.
In my original post and the current post, actually my main purpose is that I want to use Macro to add one empty row after each row which contains “Total” as my boss wants to make worksheet cleaner.
Thanks.
Dennis
 
Upvote 0
This should work for you.

Code: 
Sub lineemup()
    Dim r As Integer, Count As Integer
    Application.ScreenUpdating = False
    Count = Range("a65536").End(xlUp).Row
    For r = Count To 1 Step -1    ' work backwards to account for added lines
        If Cells(r, 1) <> vbNullString Then  ' skip blank lines
            If UCase(Cells(r, 1)) = "TOTALS" Then ActiveSheet.Rows(r + 1).Insert
        End If
    Next r
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Macro Add row
Replies
0
Views
492
dennisli
D

Forum statistics

Threads
1,215,427
Messages
6,124,830
Members
449,190
Latest member
rscraig11

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