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.

 

[VoG]

Re: Macro Help: Insert blank row on data change

Try

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

 

[frntrunr2]

Re: Macro Help: Insert blank row on data change

Brilliant! I knew I could count on you folks. Thanks!

 

[MickG]

Re: Macro Help: Insert blank row on data change

Hi, "Snap"

Dim Last As Long, intRw As Integer
Last = Range("B" & Rows.Count).End(xlUp).Row
    For intRw = Last To 2 Step -1
        If Not Cells(intRw, "B").Offset(-1) = Cells(intRw, "B") Then
            Rows(intRw).EntireRow.Insert shift:=xlUp
        End If
    Next intRw

Mick

 

[stevejd]

Re: Macro Help: Insert blank row on data change

Hi all,
how can I change this macro to insert blank rows when the value in column B changes (as per the original request) but starting in row 5 so it doesn't insert rows into the headings/title?

Any help much appreciated - thanks!

 

[VoG]

Re: Macro Help: Insert blank row on data change

Try

Sub InsBl()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 6 Step -1
    If Range("B" & i).Value <> Range("B" & i - 1).Value Then Rows(i).Insert
Next i
End Sub

 

[stevejd]

Re: Macro Help: Insert blank row on data change

That's spot on Peter, many thanks for your quick reply!

Steve

 

[chuayl]

Re: Macro Help: Insert blank row on data change

Hello, I am trying to create a similar code
In addition to the code provided, I was hoping to create autosum of the values in the same group.
The data to sum up appears from columns 29 to 49...

I've tried to modify codes from other posts but havent been successful.. Thanks!

Try

Sub InsBl()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 6 Step -1
    If Range("B" & i).Value <> Range("B" & i - 1).Value Then Rows(i).Insert
Next i
End Sub

 

[lorentej]

Re: Macro Help: Insert blank row on data change

Good Afternoon,

I am trying to enter 599 blank rows on column A every time the data changes. I have the Macro to enter 1 blank row but my project requires to enter 599 rows. Any help will be greatly appreciated. I tried to tweak the Macro but I was not successful.

Thank you,
J

 

[levanoj]

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).Insert
Next i
End Sub