Inserting extra lines for comments in an excel sheet

[LittleAnn]

HI

I have a large excel sheet which runs into about 1,000 lines with various
information in relation to figures and peoples jobs. My problem is that I
need to insert an extra row after each individual row to insert comments and
furhter information that relate to the line above it, is there a faster way
of completing this without having to go through each individual line.

 

[Gary''s Student]

run this small macro:

Sub routine()
Set r = ActiveSheet.UsedRange
nlastrow = r.Rows.Count + r.Row - 1
For i = nlastrow To 1 Step -1
Cells(i, 1).Select
Selection.EntireRow.Insert
Next
End Sub

 

[LittleAnn]

That worked thanks a million.

Just one other thing, in some of the inserted rows the comments will be the
same, is there any format of a macro that could also be used to insert text
only in the second inserted lines as was inputted from the macro below to
save time in typing it into every line??

 

[Gord Dibben]

Sub routine()
Set r = ActiveSheet.UsedRange
nlastrow = r.Rows.Count + r.Row - 1
For i = nlastrow To 2 Step -1
Cells(i, 1).Select
Selection.EntireRow.Insert
Cells(i, 1).Value = "this is a comment"
Next
End Sub

Note the change in For i = nlastrow To 2 Step -1

So's you don't insert a row above A1

This places a comment in every inserted row.

You said "some of the inserted rows" but that is too undetailed to work with.


Gord Dibben MS Excel MVP