Free Microsoft Excel 2013 Quick Reference

Inserting rows after subtotals in macro Results

i have a macro that ends with:

Range("A2").Select
    Range("A1:L82").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(7), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
there are different amounts of data each time so the destination rows to insert at are always different. how can i insert a row after each subtotal?

thanks,

brett

Can anyone help with a macro that will insert 2 blank rows after each instance of "Total" in Column D. The sheet has been subtotaled, so the total in column D will vary, 01 Total, 02 Total, etc. Thank you!!!

Good afternoon!
Could you please help me?
I have a file with numbers and I need insert empty row between each "Entity"
Entity Sub Solut Amount Amount
065 078 0 (177.74) (177.74)
065 176 9,391.08 9,391.08
066 050 10,414.71 10,414.71
066 076 33,620.13 33,620.13
066 171 10,428.90 10,428.90
066 280 6,600.00 6,600.00
078 065 (177.74) (177.74)
078 390 (19,337.90) (19,337.90)
078 079 (8,803.00) (8,803.00)
078 070 75,894.79 75,894.79
078 026 17,854.73 17,854.73
078 081 8,803.00 8,803.00
078 386 (858.10) (858.10)
078 392 101,027.64 101,027.64
078 393 (72.34) (72.34)
078 080 (34.60) (34.60)
078 082 48,712.50 48,712.50
078 020 (0.12) (0.12)
176 065 9,391.08 9,391.08
176 026 4,800.00 4,800.00
176 020 1,582.05 1,582.05
012 155 (1,277.75) (1,277.75)

I can do it this manually using subtotal formula ( see results):
Entity Sub Solut Amount Amount
065 078 0 (177.74) (177.74)
065 176 9,391.08 9,391.08

066 050 10,414.71 10,414.71
066 076 33,620.13 33,620.13
066 171 10,428.90 10,428.90
066 280 6,600.00 6,600.00

078 065 (177.74) (177.74)
078 390 (19,337.90) (19,337.90)
078 079 (8,803.00) (8,803.00)
078 070 75,894.79 75,894.79
078 026 17,854.73 17,854.73
078 081 8,803.00 8,803.00
078 386 (858.10) (858.10)
078 392 101,027.64 101,027.64
078 393 (72.34) (72.34)
078 080 (34.60) (34.60)
078 082 48,712.50 48,712.50
078 020 (0.12) (0.12)

176 065 9,391.08 9,391.08
176 026 4,800.00 4,800.00
176 020 1,582.05 1,582.05

012 155 (1,277.75) (1,277.75)

Can you help me with macros to insert empty row if entity number is different?
Thank you in advance.

I am new to these forums and this is my first post.
Oh, and I'm not really all that familiar with a lot of things in Excel. I do know how to make a macro, which this question is to help with, but I kinda skipped a lot of learning in between.

I am wanting a shortcut that will insert a new row after each change in column A. I don't think Subtotals is the answer because after I enter the row to seperate them I will want to enter another row and seperate again by column F when there is a change that is not a blank row.

Good morning, I have been looking around on the forum for a while now and it has been extremely helpful but my current project I am working on is over my head on using VBA to make it quicker and easier. I have attached a sample sheet and I have to do this report on a daily basis and it has 1,000+ lines and is time consuming. I assume there is an easier way to do this and hoping someone here could help.

What i need to do is delete all rows that have complete in column f
Sort based upon column K and F and insert a blank row after each new state (column K) and a row after the heading that is merged and says what is in column f, place a subtotal row at the bottom of each new job status in each state and total columns o,p and r.

In the attached example i need to insert a blank row in 2 and merge the cells with the text in merged cell saying Remodeled and then i would have another merged row at the first IA job that is new construction. As well as enter subtotal rows for each job status in each state then i would need a blank row after the entry for Iowa and repeat above steps for next state till complete.

After complete all states i need to delete columns b,c,d,e,k,l

Hope this is clear and any help would be appreciated

Thanks
Cory

Hi,

I am trying to do a report where I have used subtotal function. I want to insert a row after each subtotal. Would anyone be able to give me a VBA function to include in my macro so I can automate this?

Thanks in advance

Hi guys, i couldn't get an answer from another forum for this. i'm doing this macro that will insert rows for those I want to. Basically it's those rows after the subtotals.
My file is in the attachment & I would have a formula which reads "Insert Rows" so I can filter them, select visible cells & insert:

The VBA for the filtering & deleting:
Selection.AutoFilter Field:=4, Criteria1:="Insert Row"
Rows("4:10").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Insert Shift:=xlDown
The error that appeared:
Run-time error '1004':
Insert method of Range class failed

I am new to the VBA code and trying to learn, i have a current project that involves 20k+ rows of data from invoices. Column B has my invoice # in it and there may be 2-5 rows per invoice. What i need to do is insert two blank rows after each invoice. Additionally I have to sum Column G on each new line I enter which subtotals my invoice amount, I don't know if its possible to do all this with a macro or if I will have to do it manually.

thanks for the help

Hi,

On the attached sample report, I'm trying to achieve the highlighted areas for each Division (in column E; e.g. 1-000, 2-000, and so on). I have a macro in place to insert blank rows but these blank rows are where my formulas supposed to go. The division subtotal line (in bold) was achieved by using the Subtotal feature in Excel.

The following is what I need:
A blank row after the last cost code (column B; e.g. 1-905 for Div. 1-000)The category codes EQ, GE, LA, MA, and SC have to be displayed in the rows one after the other in column CIn column G, the corresponding category description to the category codes in column C must be displayed along with the word Total e.g. "Total Equipments" for EQCategory subtotals only for the non-percentage columns after G (so all columns from H-V except for columns P and T)
This part is crucial as each line item should only be summed according to category and divisionA blank row after the division subtotalFormatting-wise, it has to be displayed exactly like it is except for the light orange highlighting (blue and italicized)
Please note that the last cateogry subtotals has to have single accounting underline

I am a novice when it comes to VBA and have found many helpful posts on this site. Unfortunately After much searching I have not found this answer. I have a set of spreadsheets that I am creating a macro for. The sheet reports Overtime data for the month for multiple teams.

For this section I am having trouble with this is what I want my macro to do. I was able to accomplish steps 1 and 2.

1. The data is sorted by Pay period End Date.
2. Then 3 rows are inserted between changes in PP end Date.
3. Total all data in column F and Column G in the first empty row after each date change.
* The number of rows will change from month to month. The constant I have will be the 3 rows after the date change will be empty.

Below is an example of the code that I have. I would like to accomplish this without using the subtotal feature.


	VB:
	
 CopySort() 
     ' This section Sorts the data on the spreadsheet
     ' by Pay Period End Date "J" then Employee ID "E"
     '
    Range("A2").Select 
    Range("A1:L500").Sort Key1:=Range("J2"), Order1:=xlAscending, Key2:=Range( _ 
    "E2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ 
    :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers, _ 
    DataOption2:=xlSortTextAsNumbers 
     '
     ' This next section will insert 3 blank rows between
     ' Changes in Pay period End dates
     '
    Dim rngA As Range 
     
    Set rngA = Range("J2") 
     
    While rngA.Value  "" 
        If rngA.Value  rngA.Offset(1).Value Then 
            rngA.Offset(1).Resize(3).EntireRow.Insert 
            Set rngA = rngA.Offset(3) 
        End If 
        Set rngA = rngA.Offset(1) 
    Wend 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a workbook with a number of spreadsheets that I would like to improve the readability by adding a few blank rows. Column A is a number code; B is a date, C a description, D an amount. After each code grouping there is a subtotal. I am trying to create a macro that would simply add a line before and after each row that contains “SubTotal” in Column B, and finally add the Grand Total of all of the subtotals in Column D at the bottom separated from the rest by a row as well.

The sheet can have any number of code groups and any number of dates and descriptions within the group. Here is a what it looks like.

987___1-Jun-04____red____35
987___4-Jun-04____blk____22
______SubTotal___________57
654___2-Jun-04____brn____10
654___9-Jun-04____gry____99
654___7-Jun-04____red____11
654___5-Jun-05____blu____15
______SubTotal__________135

Here is what I have I have so far, but it sure isn’t doing what I am after. I am getting 2 lines above the subtotal line and none for the last grouping. Thought that I should get this sorted out before I try the rest.


	VB:
	
 addBlankRows() 
    Dim wrkSht As Worksheet 
    Dim x As Long 
    For Each wrkSht In ActiveWorkbook.Worksheets 
        If wrkSht.Name  "lookup" Then 
            For x = Range("A65536").End(xlUp).Row To 5 Step -1 
                If Cells(x, 2) = "SubTotal" Then 
                    Rows(x).Insert 
                End If 
            Next x 
        End If 
    Next wrkSht 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Could someone please give me a nudge in the right direction?

Thanks,
hap

Is there any way to insert 7 blank rows after I run subtotals?

I tried creating a macro, but it kept inserting the lines at the same spot rather than where I pointed the cursor to.

Edit : Subject title
Jack in the UK

I need a macro that will insert a page break each time it finds the string "total" in column D. The data starts in row 6, and as there are subtotals in more than one column, which is why I need it to insert only when "total" comes up in column D. Also, since it's been subtotaled, the verbiage varies (i.e., O1 Total, 02 Total, etc.). Thanks!

Hi,

I've been trying to create a macro for the attached spreadsheet that would insert 6 blank rows before each division subtotal (e.g. 1-000 Total) and 1 blank row after it.

I've solved the above ^ problem BUT only the 6 blank rows before each division subtotal. I just don't know exactly how to add another IF statement to this for the 1 blank row after.

Also, I am using Office Connector in connection with Timberline Software. Is it possible to include a SQL code within my macro?

Note:
*The "Grand Total" has to have 7 blank rows before and 1 blank row after it.

Can someone please help out? It would be greatly appreciated.

Cheers,
Donna

Report - Values.xlsx

Loacation Name Sales Comm
252 ABC 100 12.5
252 CCC 100 12.5
252 DDD 100 12.5
300 EEE 100 12.5
300 FFF 100 12.5
500 GGG 100 12.5
500 HHH 100 12.5
500 KKK 100 12.5

This is the data I got from external databae query. Is there any VBA code to add subtotal and also running total in the rows after evry location number changes. I mean, If I run the vba macro It will insert two rows after every location changes. One row for subtotal for the each locaion and in the next row will be the cumulative totals for all location upto that point.

Hello,

I'm hoping this can be done with a macro. I'm attaching a file to illustrate my needs.

I start with the file as it is shown on the Original tab.
Then, I do a subtotal so that it looks like the data in the Subtotal tab.

What I then need help doing is ...
1. Deleting all data from the first column except for the subtotal references
2. Inserting a blank row each time a new subtotal element begins.
My final result is in the formatted tab.

Is this possible without doing it manually?

Thank you for your help!!

Bill

Hi there,

I have a macro that formats a spreadsheet. To finish it off I need code that does the following.

1) Adds subtotals and a grand total, in bold.

2) Inserts a blank row after each subtotal.

3) After the macro has run and the spreadsheet is formatted, any change to the "Amount" column will also update the subtotal and grand total.

Can anyone help please?

Thnak you.

Item PO Date Amount
PEMS2714 3000086401 24/06/2008 10.00
PEMT6781 3000087313 10/07/2008 15.00
67679 3000087313 10/07/2008 20.00
Jul-08 45.00

PEMV8998 ST17893 6/08/2008 20.00
AMAL2773 3000093472 14/08/2008 30.00
PEMZ8397 3000096172 23/09/2008 40.00
Sep-08 90.00

PENA4834 3000098208 1/10/2008 5.00
PENA5583 3000098772 2/10/2008 25.00
PENA6627 3000098884 3/10/2008 50.00
PENA9688 3000097272 7/10/2008 100.00
Oct-08 180.00

PEND2968 3000100872 3/11/2008 50.00
PEND2967 3000100872 3/11/2008 150.00
Nov-08 200.00

Total 515.00

Sample data :

COLORADO 014C 2391942 3528600 141
172 724899 1067108 40
1797 546812 820255 34
1998 416032 626383 28
2216 806535 1176044 44
237 1872461 2710125 104
2477 168605 253895 10
2830 1016305 1527912 64
3262 396654 557467 20
3524 1261749 1913467 79
3525 1704009 2442987 99
3609 614421 914285 33
3641 1204536 1760984 73
3704 1914318 2761913 109
3748 913416 1333302 54
3934 1355085 2068155 85
3938 857785 1273634 57
COLORADO TOTAL 18165564 26736516 1074
36331128 53473032 2148

Other info:

1. I used the subtotal command to get the last line of data. Note that the
row above it is the subtotal already in the spreadsheet.

2. In column a, there is a number that reflects the state in column B

3. Columns C- F have data that gets subtotaled.

How do I create a macro that:

1. inserts a row above and below where each subtotal occurs. Keep in mind it
would be a change in Column A (would prefer B).

2. After inserting the rows , I then need to delete the rows that has the
State Total already in the spreadsheet

3. Or better yet: lookup value in column a, preplace blank in column b with
dat from lookup table. At change in column b, insert row. Needs to be done in
this order, as if this is done before lookup/replace, I will have a break
from "state" and state total"

Thanks..

each time a number changes in a designated column? Basically I have a very
large spreadsheet that is difficult to read. I have been asked to make a
macro that inserts three blank rows every time the number in my ITEMNBR
column changes. The trouble is I don't know how to ask Excel to look for a
change in number. The only function in Excel that I know of that does this is
the Subtotals function. I tried using it and putting the subtotal in a new
column, which I would then hide, but the function puts information in the
rows below each new number, so that doesn't help.

The Subtotals function: "At each change in" is what I need, but after that I
need "add three rows", not a mathematical function.

Does anyone know how to program this into a macro? I don't know Visual Basic
and have no idea what the coding would be.

Thanks.

Hello,

Can someone assist me with creating a macro that will sort by Column A, insert a blank row after each different criteria in Column A, creating a subtotal for the amounts located in Column d for all matching criteria in Column A and then insert total in Column E.

What I have

Column A Column d
111 300
111 20
111 105
122 75
122 55
122 50
133 80
133 120
133 15

What I need
Column A Column D Column E
111 ----------300
111 ----------20
111----------105
--------------------- 425
122----------75
122----------55
122----------50
--------------------- 180
133 ----------80
133 ----------120
133 ----------15
--------------------- 215
Thank you for any assistance you can provide.