Macro: Insert blank row on data change

[frntrunr2]

Hi Folks,

I'm trying to come up with a macro to insert a blank row when data in column B changes.
Column B contains Fund Codes and I want to insert a blank row between each of the funds for easier reading. I've searched past posts but was unable to modify anything that I saw to work for me.

As always, any help is GREATLY appreciated. Thanks.

 

[kalak]

Re: Macro Help: Insert blank row on data change

Could anyone assist to adjust the following code to instead of inserting just 1 blank row insert 10 blank rows instead?

Sub InsertBlankRow()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    If Range("A" & i).Value <> Range("A" & i - 1).Value Then Rows(i).Resize(10).Insert
Next i
End Sub

 

[levanoj]

Re: Macro Help: Insert blank row on data change

Awesome! that did the trick, thanks Kalak

 

[InsBl]

Re: Macro Help: Insert blank row on data change

Apologies for bumping old thread, but the code here gets me almost all the way home.

I would like to insert a blank row when the value in column B changes, but only when a part of the value is changed. As below, I want the macro to only check the value before the "." when it determines whether or not to insert the blank row. Is this possible? TIA!

8534.6
8534.7
8534.8
8534.9

8727.1

9534.1

5210.19

7093.10
7093.11
7093.12
7093.13
7093.14
7093.15
7093.16
7093.17

 

[kalak]

Re: Macro Help: Insert blank row on data change

Maybe something like this.

Sub insert()
Dim c
For Each c In Intersect(Columns("b"), ActiveSheet.UsedRange)
    If c <> "" And c(2) <> "" And Int(c.Value) <> Int(c(2).Value) Then
        Rows(c(2).Row).insert
        Set c = c(0)
    End If
Next
End Sub

 

[Stryka]

Re: Macro Help: Insert blank row on data change

Thanks for the code,

I assume this requires all the cells above B6 to have a value in them. My Spreadsheet has a table that has headings in R13, a total sum in R16 and there are a few empty cells in column B above it that are empty (4 in fact). How would I set it up so that when I type something in the table in column B it adds a new row? (ideally I want to keep a blank between the data and the table sum row at the end).