Macro to insert 'n' rows when column value changes

[davio565]

Hi,

I have some sheets I need to seperate the data so that when column a changes value rows are inserted. So for example if column a is:

Aa
Aa
Aa
Bb
Bb
Cc
Cc
Cc
Cc
Cc
Dd

Then the macro would insert different numbers of row beneath each value change such that each first new value would start on every nth row (say 10 as below):

Aa - row 1
Aa
Aa

Bb - row 11
Bb

Cc - row 21
Cc
Cc
Cc
Cc

Dd - row 31

I think it may be tricky as I asked this before but no one answered to I look forwards to any suggestions.

Thanks

 

[My Aswer Is This]

Try this:
Will insert 10 rows when value in column A changes

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Row <> 1 Then
If Target.Value <> Target.Offset(-1).Value Then Rows(Target.Row).Resize(10).Insert
End If
End If
End Sub

 

[davio565]

Thanks for this.

The code does what I need it to but not quite how I need it to. So I basically want to copy and paste my example above into my sheet and then the macro would run on the pasted values. The one here does work but only if i were to type in my values. Sorry I'm not the best at VBA I'm sure there is an easy way to change the code you posted. Basically, it would run after all the data is pasted into the sheet and could either be instantaneous or one that I set off through the developer tab.

Thanks

 

[davio565]

Hi sorry just realised it doesn't quite do what i need.

So instead of inserting 10 rows, i need it to insert different numbers of rows, so that the first unique value in column A is the same number of rows away from the next unique value in A irrespective of how many duplicate values there are away.

The code you posted would result in my example being as follows:

Aa - row 1
Aa
Aa

Bb - row 14
Bb

Cc - row 26
Cc
Cc
Cc
Cc

Dd - row 41

I need it to be:

Aa - row 1
Aa
Aa

Bb - row 11
Bb

Cc - row 21
Cc
Cc
Cc
Cc

Dd - row 31

But i'm pasting in these values so it would be better to run it when all the data is in the sheet.

Thanks

 

[My Aswer Is This]

I'm going to let someone else here at Mr. Excel help you because I'm confused.