Insert row under each criteria

kelly mort

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
My data starts from Column A2 to J1002

So column C has the criteria say "x", "y", "z", etc.

These criteria are sorted so they are grouped.

That's all x all y all z in that order.

So I want to insert a blank row after the last x, then a blank row under the last y in that order.

Row 1 is the header

I am looking for a vba solution to this.

Thanks in advance
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
Try this:

Code: 
Sub a1090658a()
Dim i As Long
For i = 1002 To 2 Step -1
    Do While Cells(i, "C") = Cells(i - 1, "C")
        i = i - 1
    Loop
    If i = 2 Then Exit For
    Rows(i).Insert xlUp
Next
End Sub
 
Upvote 0
Unbelievable! !!

Very brilliant.


One last thing :

From column D to J are values that needs to be added.

And they are supposed to be placed inside those blank rows.

That's all x added all y added etc.

I am sure this is possible as well.

Thanks
 
Upvote 0
Do you mean value of col C will be copied to col D:J on the blank row?
Say there are only 3 "x" in col C, so col D:J will have "x" or only col D:F?
 
Upvote 0
Akuini said:
Do you mean value of col C will be copied to col D:J on the blank row?
Say there are only 3 "x" in col C, so col D:J will have "x" or only col D:F?
Click to expand...


I am adding all the column D values for "x" and place it inside that empty cell under the "x". Then I do same for E, F, ...J.

Then with criteria "y" , we do same as above. In that order.

That's finding Sub totals .
 
Upvote 0
Ok, try this:

Code: 
[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1090658b()
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], j [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], k [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] q [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], x [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] r [COLOR=Royalblue]As[/COLOR] Range
[COLOR=Royalblue]Dim[/COLOR] va

Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]
[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1002[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]2[/COLOR] [COLOR=Royalblue]Step[/COLOR] -[COLOR=crimson]1[/COLOR]
    [COLOR=Royalblue]Do[/COLOR] [COLOR=Royalblue]While[/COLOR] Cells(i, [COLOR=brown]"C"[/COLOR]) = Cells(i - [COLOR=crimson]1[/COLOR], [COLOR=brown]"C"[/COLOR])
        i = i - [COLOR=crimson]1[/COLOR]
    [COLOR=Royalblue]Loop[/COLOR]
    [COLOR=Royalblue]If[/COLOR] i = [COLOR=crimson]2[/COLOR] [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]For[/COLOR]
    Rows(i).Insert xlUp
[COLOR=Royalblue]Next[/COLOR]

n = Range([COLOR=brown]"C"[/COLOR] & Rows.count).[COLOR=Royalblue]End[/COLOR](xlUp).Row
q = [COLOR=crimson]2[/COLOR]

[COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] r [COLOR=Royalblue]In[/COLOR] Range([COLOR=brown]"C2:C"[/COLOR] & n + [COLOR=crimson]1[/COLOR]).SpecialCells(xlCellTypeBlanks)
    [COLOR=Royalblue]For[/COLOR] k = [COLOR=crimson]4[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]10[/COLOR]
        x = r.Row
        Cells(x, k) = WorksheetFunction.Sum(Range(Cells(q, k), Cells(x - [COLOR=crimson]1[/COLOR], k)))
    [COLOR=Royalblue]Next[/COLOR]
    q = x + [COLOR=crimson]1[/COLOR]
[COLOR=Royalblue]Next[/COLOR]

Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]
 
Upvote 0
More than expected! !!

Thanks for the time and effort spent.
 
Upvote 0
You're welcome, glad to help, & thanks for the feedback.:)
 
Upvote 0
Akuini said:
Ok, try this:

Code: 
[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1090658b()
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], j [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], k [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] q [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], x [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] r [COLOR=Royalblue]As[/COLOR] Range
[COLOR=Royalblue]Dim[/COLOR] va

Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]
[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1002[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]2[/COLOR] [COLOR=Royalblue]Step[/COLOR] -[COLOR=crimson]1[/COLOR]
    [COLOR=Royalblue]Do[/COLOR] [COLOR=Royalblue]While[/COLOR] Cells(i, [COLOR=brown]"C"[/COLOR]) = Cells(i - [COLOR=crimson]1[/COLOR], [COLOR=brown]"C"[/COLOR])
        i = i - [COLOR=crimson]1[/COLOR]
    [COLOR=Royalblue]Loop[/COLOR]
    [COLOR=Royalblue]If[/COLOR] i = [COLOR=crimson]2[/COLOR] [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]For[/COLOR]
    Rows(i).Insert xlUp
[COLOR=Royalblue]Next[/COLOR]

n = Range([COLOR=brown]"C"[/COLOR] & Rows.count).[COLOR=Royalblue]End[/COLOR](xlUp).Row
q = [COLOR=crimson]2[/COLOR]

[COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] r [COLOR=Royalblue]In[/COLOR] Range([COLOR=brown]"C2:C"[/COLOR] & n + [COLOR=crimson]1[/COLOR]).SpecialCells(xlCellTypeBlanks)
    [COLOR=Royalblue]For[/COLOR] k = [COLOR=crimson]4[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]10[/COLOR]
        x = r.Row
        Cells(x, k) = WorksheetFunction.Sum(Range(Cells(q, k), Cells(x - [COLOR=crimson]1[/COLOR], k)))
    [COLOR=Royalblue]Next[/COLOR]
    q = x + [COLOR=crimson]1[/COLOR]
[COLOR=Royalblue]Next[/COLOR]

Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]
Click to expand...


Hello
Is there a way to add all the "sub total" into a cell say H2?

That's we add all those values in the sub total calculations we did into cell H2.

Regards


Kelly
 
Upvote 0
Ok, this is untested:

Code: 
[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1090658b()
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], j [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], k [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] q [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], x [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], z [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] r [COLOR=Royalblue]As[/COLOR] Range
[COLOR=Royalblue]Dim[/COLOR] va

Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]
[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1002[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]2[/COLOR] [COLOR=Royalblue]Step[/COLOR] -[COLOR=crimson]1[/COLOR]
    [COLOR=Royalblue]Do[/COLOR] [COLOR=Royalblue]While[/COLOR] Cells(i, [COLOR=brown]"C"[/COLOR]) = Cells(i - [COLOR=crimson]1[/COLOR], [COLOR=brown]"C"[/COLOR])
        i = i - [COLOR=crimson]1[/COLOR]
    [COLOR=Royalblue]Loop[/COLOR]
    [COLOR=Royalblue]If[/COLOR] i = [COLOR=crimson]2[/COLOR] [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]For[/COLOR]
    Rows(i).Insert xlUp
[COLOR=Royalblue]Next[/COLOR]

n = Range([COLOR=brown]"C"[/COLOR] & Rows.count).[COLOR=Royalblue]End[/COLOR](xlUp).Row
q = [COLOR=crimson]2[/COLOR]

[COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] r [COLOR=Royalblue]In[/COLOR] Range([COLOR=brown]"C2:C"[/COLOR] & n + [COLOR=crimson]1[/COLOR]).SpecialCells(xlCellTypeBlanks)
    [COLOR=Royalblue]For[/COLOR] k = [COLOR=crimson]4[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]10[/COLOR]
        x = r.Row
        Cells(x, k) = WorksheetFunction.Sum(Range(Cells(q, k), Cells(x - [COLOR=crimson]1[/COLOR], k)))
        z = z + Cells(x, k)
    [COLOR=Royalblue]Next[/COLOR]
    q = x + [COLOR=crimson]1[/COLOR]
[COLOR=Royalblue]Next[/COLOR]
Range([COLOR=brown]"H2"[/COLOR]) = z
Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

Edit:
If your data has decimal number, then change:
"z As Long" to "z As Double"
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Compare cell in column to cells below
2
Replies
13
Views
585
kevin9999
K
H
  • Solved
Amend the VBA code to Filter Columns Instead of Rows
Replies
9
Views
136
Akuini
Akuini
S
  • Solved
How to combine IF and FILTER formulas to copy text from one column to another
Replies
4
Views
224
Fluff
Fluff
theYaniac
  • Solved
I would like to insert a blank row between each new unique identifier in column A.
Replies
3
Views
146
MARK858
MARK858
D
  • Locked
  • Question
Range in worksheet1 Insert into worksheet2 from row 2
Replies
1
Views
155
Fluff
Fluff

Forum statistics

Threads
1,214,525
Messages
6,120,052
Members
448,940
Latest member
mdusw

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