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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Hi - Welcome to the board

This would need to be done with vba. Where do you want the rows to be inserted?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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