add rows to a sheet
 

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.

add rows to a sheet
 

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



"alvin Kuiper" wrote:

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.

add rows to a sheet
 

I don't know if you have a header row. The code below assumes there is
not header row. Let me know if there is a header row and I will make a
couple of minor changes.


Sub MoveRows()

LastRow = Range("A" & Rows.Count).End(xlUp).Row
For RowCount = LastRow To 1 Step -1
RowIndex = Range("A" & RowCount)
If RowIndex < RowCount Then
Rows(RowCount).Copy Destination:=Rows(RowIndex)
Rows(RowCount).ClearContents
End If
Next RowCount
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=178433

Microsoft Office Help

add rows to a sheet
 

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).

--
Rick (MVP - Excel)


"alvin Kuiper" wrote in message
...
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.