Inserting rows in Excel

G

Guest

Guest
I was wondering if anyone knows how to tell excel to look at a column and insert a row in between each different value
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
If I understand right, this Marco Written by Barrie Davidson will do what you want
Sub Insert_row()

' Written by Barrie Davidson

Dim Number_of_rows As Long

Dim Rowinsert As Integer

Number_of_rows = Range("A65536").End(xlUp).Row

Ans = InputBox("How many rows do you want to insert ?", "Rows", 1)

If IsNumeric(Ans) Then

Rowinsert = CInt(Ans)

Else

MsgBox "Invalid number entered", vbCritical, "Error"

Exit Sub

End If

Range("A2").Select

Do Until Selection.Row = Number_of_rows + 1

If Selection.Value <> Selection.Offset(-1, 0).Value Then

Selection.EntireRow.Resize(Rowinsert).Insert

Number_of_rows = Number_of_rows + Rowinsert

Selection.Offset(Rowinsert + 1, 0).Select

Else

Selection.Offset(1, 0).Select

End If

Loop

End Sub
 
Upvote 0
Thanks, Paul B it worked great and will save me a lot of time. Again, THANKS!!!!!
 
Upvote 0
That is great. How would I tweak this to only insert one row at a time beneath a highlighted cell?
 
Upvote 0
Select a cell.
Turn on the macro recorder.
Goto Inset>Rows.
Turn off the macro recorder.

You now have your macro.
 
Upvote 0
Automatically Insert an X number of rows

Hi,
Further to the solution given below, I have pasted the VDB code but not getting the desired results. I want to insert 2 blank after each row with data in it. e.g

aa
a
a

should look like:
aa


a


a

after the auto insert of rows. Can anyone help pls? :eek:
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

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