how to insert rows

aberfoyle

Board Regular
Joined
Apr 19, 2005
Messages
60
Hi, I am a basic excel user. I have a spreadsheet with 700 rows of data. I need to insert 11 rows between each row. Is there an easy way to do this without writing a macro or using VBA since I don't know how to do these.

Thanks
 
and now you understand how it works, here is some code :)
Code:
Option Explicit

Sub make_selection_to_add_blank_rows()
'Erik Van Geit
'060630

Dim tmp As Range
Dim rng As Range
Dim FR As Long          'First Row
Dim LR As Long          'Last Row
Dim CR As Long          'Count Rows
Dim NR As Integer       '# rows to insert

    On Error Resume Next
    Set tmp = Application.InputBox(prompt:="Select the range where you want to insert rows", _
    Title:="SELECTION", Default:=Selection.Address, Type:=8)
    On Error GoTo 0
    If tmp Is Nothing Then Exit Sub

FR = tmp(1).Row
CR = tmp.Rows.Count
LR = FR + CR - 1

    NR = Application.InputBox("Please enter the number of rows to insert", "# ROWS", Type:=1)
    If NR = False Then
    MsgBox "No rows will be inserted", 48, "Operation aborted"
    Exit Sub
    End If

Application.ScreenUpdating = False

Columns(1).Insert
Set rng = Range(Cells(FR, 1), Cells(LR, 1))

    With rng
    Cells(FR, 1) = 1
    .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1
    Rows(LR + 1 & ":" & LR + NR * CR).Insert Shift:=xlDown
    .Copy .Offset(CR, 0).Resize(CR * NR, 1)
    .Resize(CR * (NR + 1)).EntireRow.Sort Key1:=Cells(FR, 1), Order1:=xlAscending, Header:=xlNo
    .EntireColumn.Delete
    End With

Application.ScreenUpdating = True

End Sub
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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