Inserting extra lines for comments in an excel sheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LittleAnn - 31 Oct 2007 11:26 GMT

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.

Reply to this Message

Gary''s Student - 31 Oct 2007 13:58 GMT

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

Signature

Gary''s Student - gsnu200752

> HI
>
[quoted text clipped - 3 lines]
> 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.

Reply to this Message

LittleAnn - 31 Oct 2007 23:01 GMT

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??

> run this small macro:
>
[quoted text clipped - 14 lines]
> > 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.

Reply to this Message

Gord Dibben - 01 Nov 2007 00:31 GMT

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

>That worked thanks a million.
>
[quoted text clipped - 21 lines]
>> > 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.

Reply to this Message