add rows to a sheet

[alvin Kuiper]

Hi If I have this in a sheet:
1
5
8
10
15
25
27
30
What i want is a macro there can make empty rows between the number so there
come 3 empty rows between 1 and 5 and 1 empty row between 8 and 10 - and so
on, I don't know how many rows there are numbers in:
Hope some one understand and maybe also can help.

 

[Simka]

Try this... (Put the cursor in on the first value - 1) then run the macro
until the end.

Sub InsertingLines()

Dim Firstvalue As Integer
Dim Secondvalue As Integer
Dim Result As Integer
Dim NoofRows As Integer
Dim Number As Integer

Firstvalue = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Secondvalue = ActiveCell.Value
Result = Secondvalue - Firstvalue
NoofRows = Result - 1
For Number = 1 To NoofRows
Selection.EntireRow.Insert
Next
Selection.End(xlDown).Select

End Sub

 

[Rick Rothstein]

You can use this macro to do what you want...

Sub InsertRows()
Dim X As Long, LastRow As Long
Const FirstRow As String = 2
Const DataColumn As String = "A"
LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
For X = LastRow To FirstRow + 1 Step -1
With Cells(X, DataColumn)
.Resize(.Value - .Offset(-1).Value - 1).EntireRow.Insert
End With
Next
End Sub

Just set the two Const values to match your setup (FirstRow is the row
number your starting number is in, DataColumn is the column letter your
numbers are in), the code will adjust itself around the values you set these
to. Also note that your number does not have to start at 1... the code will
work around whatever your starting number is (that is, your column numbers
could be 8, 10, 15, 25 and the appropriate number of rows will be inserted
between them).