macro to insert rows after each change in a sorted list

KarenM

New Member
Joined
Mar 5, 2004
Messages
2
i need to extend a simple macro which sorts a list and get it to also insert a blank row after each change in the sorted column. a bit like subtotals, but with blanks instead of subtotals subtotals.

can it be done just in excel, or will it have to be done in vba?

any suggestions?

thanks
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
Hi - Welcome to the board

This would need to be done with vba. Where do you want the rows to be inserted?
 

GaryB

Active Member
Joined
Feb 25, 2002
Messages
459
I think you'll need to do this in VBA

Here's a simple one that should work for you. This assumes your list is in Column A, you would need to amend the Cells(myRow,1) to suit if its in a different column.


Code:
Sub SplitList()
'
' SplitList Macro
' Macro recorded 05/03/2004 by GaryB
'
Dim myRow As Long

myRow = 3   'or use 2 if you haven't got a header
Do Until Cells(myRow, 1) = ""
If Cells(myRow, 1) = Cells(myRow - 1, 1) Then
myRow = myRow + 1
Else
Cells(myRow, 1).EntireRow.Insert
myRow = myRow + 2
End If
Loop
'
End Sub

Hope This Helps

GaryB
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Seems sensible to add to the code :-

Code:
Sub test()
    rw = 1
    myval = ActiveSheet.Cells(rw, 1).Value
    While myval <> ""
        While myval = ActiveSheet.Cells(rw, 1).Value
            rw = rw + 1
        Wend
        ActiveSheet.Rows(rw).EntireRow.Insert
        rw = rw + 1
        myval = ActiveSheet.Cells(rw, 1).Value
    Wend
End Sub
 

KarenM

New Member
Joined
Mar 5, 2004
Messages
2

ADVERTISEMENT

thanks garyb and brianb.

both your codes worked great and i managed to adjust them to work for different columns and insert 2 rows each time.

now i'm spoilt for choice as to which one i should use!!!

cheers :wink:
 
L

Legacy 21301

Guest
This can be done manually, without a macro - here's a macro based on a manual method that uses worksheet formulas, sequential numbering, and sorting.

If there are a lot of data rows to be processed, the following should be materially quicker than code that uses a loop.

Assumed that row 1 is a header row :-

Code:
Sub Insert_Blank_Rows()
Dim rng As Range, cell As Range
Application.ScreenUpdating = False
Columns(1).Insert
[A2] = 1
Set rng = Range([A3], [B65536].End(xlUp)(1, 0))
With rng
    .FormulaR1C1 = "=IF(RC[1]=R[-1]C[1],R[-1]C,R[-1]C+1)"
    .Value = .Value
End With
Set cell = [A65536].End(xlUp)
cell(2) = 1
cell(2).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=cell - 1
Range([A2], [A65536].End(xlUp)).EntireRow.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo
Columns(1).Delete
Application.ScreenUpdating = True
End Sub
 

gwynwjones

Board Regular
Joined
Mar 17, 2004
Messages
66

ADVERTISEMENT

Hi there,

Like the macro but is it possible to sum an amount after each new insert.

I have a list of data and need to insert a new line for each new number (as in the current macro) and also sum a different column.

account num bill value
620013199232 30 £30.40
620014507953 32 £4.63
620015266401 1 £4.57
620030120195 1 £5.01

New line between each bill and sum value.

Hope this makes sense.

Gwyn
 

GaryB

Active Member
Joined
Feb 25, 2002
Messages
459
I hope I'm reading the layout of your data correctly - this will subtotal Column C and insert an extra row after each SubTotal.

Code:
Sub SplitListAndSubTotal()
'
' SplitListAndSubTotal Macro
' Macro recorded 19/03/2004 by GaryB
'
Dim myRow As Long
Dim MyStart As Long

MyStart = 2
myRow = 3   'or use 2 if you haven't got a header

Do Until Cells(myRow, 1) = ""
If Cells(myRow, 1) = Cells(myRow - 1, 1) Then
myRow = myRow + 1
Else
Cells(myRow, 1).EntireRow.Insert
Cells(myRow, 3) = Application.WorksheetFunction.Sum(Range(Cells(MyStart, 3), Cells(myRow - 1, 3)))
Cells(myRow + 1, 1).EntireRow.Insert
myRow = myRow + 3
MyStart = myRow - 1

End If
Loop

Cells(myRow, 3) = Application.WorksheetFunction.Sum(Range(Cells(MyStart, 3), Cells(myRow - 1, 3)))


'
End Sub


Hope This Helps

GaryB
 
L

Legacy 21301

Guest
gwynwjones said:
Hi there,

Like the macro but is it possible to sum an amount after each new insert.

I have a list of data and need to insert a new line for each new number (as in the current macro) and also sum a different column.

account num bill value
620013199232 30 £30.40
620014507953 32 £4.63
620015266401 1 £4.57
620030120195 1 £5.01

New line between each bill and sum value.

Hope this makes sense.

Gwyn

You don't need a macro to do this - just use Xl's SubTotal tool
 

Eleni776

New Member
Joined
Oct 8, 2015
Messages
2
I think you'll need to do this in VBA

Here's a simple one that should work for you. This assumes your list is in Column A, you would need to amend the Cells(myRow,1) to suit if its in a different column.


Code:
Sub SplitList()
'
' SplitList Macro
' Macro recorded 05/03/2004 by GaryB
'
Dim myRow As Long

myRow = 3   'or use 2 if you haven't got a header
Do Until Cells(myRow, 1) = ""
If Cells(myRow, 1) = Cells(myRow - 1, 1) Then
myRow = myRow + 1
Else
Cells(myRow, 1).EntireRow.Insert
myRow = myRow + 2
End If
Loop
'
End Sub

Hope This Helps

GaryB


Hi Gary if the change was in column B instead of Column A can you show me how the VBA script should look like in your above example:

Thanks in advance!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,093
Messages
5,768,047
Members
425,451
Latest member
JohnBrooksBiddle

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
Top