Solved

# VBA Excel Insert

Posted on 2013-09-26
Medium Priority
345 Views
Hello,

I need to find the last Rows Used in Column "D",
then   add a 5 comments into Column "E"   2 rows after last Row found..

Thanks for any help.
Sample.xlsx
0
Question by:W.E.B

LVL 15

Assisted Solution

unknown_routine earned 200 total points
ID: 39525758
Here is the VBA code. Just add a button to excel file and then add this code for the button:

Private Sub CommandButton1_Click()
Dim i

Dim counter

For i = 20 To 10000
counter = i
If Cells(i, 4).Value = "" Then
Exit For
End If
Next i

For j = 1 To 5
Cells(counter + 1 + j, 5).Value = "Comment " & j

Next j

End Sub
0

LVL 10

Expert Comment

ID: 39525763
Hi,

You can use the following code on the Sheet you wish you add the comments to.

``````Option Explicit

With Range("D1048576").End(xlUp)
.Offset(3, 1).Value = "Comment 1"
.Offset(4, 1).Value = "Comment 2"
.Offset(5, 1).Value = "Comment 3"
.Offset(6, 1).Value = "Comment 4"
.Offset(7, 1).Value = "Comment 5"
End With
End Sub
``````
see attached
Sample.xlsm
0

Author Comment

ID: 39525769
Hello,

the comments are not the same.
each line has a different comment.

thanks
0

LVL 35

Accepted Solution

[ fanpages ] earned 1000 total points
ID: 39525812
Hi,

Here is another approach.

This code is within the code module, "basQ_28250815", of the attached workbook.

``````Option Explicit
Public Sub Q_28250815()

Dim lngRow                                            As Long

Worksheets("Sheet1").Select

lngRow = Cells(Cells.Rows.Count, "D").End(xlUp).Row   ' This is the last row in column [D]

Cells(lngRow + 3&, "E") = "Comment #1"
Cells(lngRow + 4&, "E") = "Comment #2"
Cells(lngRow + 5&, "E") = "Comment #3"
Cells(lngRow + 6&, "E") = "Comment #4"
Cells(lngRow + 7&, "E") = "Comment #5"

End Sub
``````

I have taken a copy of the original [Sheet1] worksheet, & named it [Sheet1 (Original)].

The current [Sheet1] worksheet in the workbook does not have any "Comments" rows in column [E], so you can see the outcome of running the above code.

(I did note, however, in your original worksheet, that there was a gap between Comments #4 & #5.  Was that intentional?)

To execute the code, please use the [ALT]+[F8] key combination to display the "Macro" dialog box, select "Q_28250815" as the "Macro name", & then click the [Run] button.

BFN,

fp.
Q-28250815.xlsm
0

LVL 35

Expert Comment

ID: 39525819
...each line has a different comment

Each of us (!) has chosen to display five comments numbered from 1 to 5.

You simply need to replace the text for each Comment as shown above with the text that you require.

FamousMortimer's code, & my proposal, make that task an easier process.
0

Author Closing Comment

ID: 39525837
Thank you very much guys.
Appreciate all your time and help.
0

LVL 35

Expert Comment

ID: 39525991
You are very welcome.

However, did you intend to miss FamousMortimer from the allocation of points?

I think he would appreciate some feedback about his solution in any respect.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.