code for insert row counter

[scott micklo]

HI. So now I am looking for the correct code to do the following (I am only using a portion of the code).

I would like to be able to insert a new row below the already input data. For my code:
lRow enters the subroutine with a value of 15 { this means the last row of data is on row 15}
I am looking to insert a row below this one so I can add more data. As the user continues, the counter (lRow) will increase each time this subroutine is called, and continue to insert the next row below the previously used on. {for clearer, I have data in row 15 and 16. The user needs to add more data, so the subroutine will add a new row row 17, and the user can input data. This will continue until the user is done.

The code I am looking for will be how to call the correct row (last used), add 1 to it, and insert that row.

Sub cmdEnterMore_Click()
'
' InsertRow
'
    lRow = lRow + 1
'
    Range("A" + lRow, "J" + lRow).Select
    Selection.EntireRow.Insert
    Range("A" + lRow, "J" + lRow).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = True
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Range("A" + lRow, "J" + lRow).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
end sub

[Olly]

How are you identifying the last data row? What is below the range where you want to insert a row?

Would be easier if you attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[scott micklo]

Thanks so much

[Mumps1]

Glad to help.

[Mumps1]

If you simply want to add your data to the first available blank row, you don't have to insert anything. Try:

Sub cmdEnterMore_Click()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("A" & LastRow + 1 & ":J" & LastRow + 1).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = True
        .ReadingOrder = xlContext
        .MergeCells = False
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
    End With
    Selection.Merge
    Application.ScreenUpdating = True
End Sub