Macro: Insert blank row on data change

frntrunr2

New Member
Joined
Jan 17, 2003
Messages
29
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.
 
Re: Macro Help: Insert blank row on data change

Try

Code:
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
 
Upvote 0
Re: Macro Help: Insert blank row on data change

Hi, "Snap"
Code:
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
 
Upvote 0
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!
 
Upvote 0
Re: Macro Help: Insert blank row on data change

Try

Code:
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
 
Upvote 0
Re: Macro Help: Insert blank row on data change

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

Steve
 
Upvote 0
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

Code:
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
 
Upvote 0
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
 
Upvote 0
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?

Code:
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
 
Last edited:
Upvote 0

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