Adding New Row In Sheet When Last Row Is Reached

R

RJPotts

Board Regular
Joined
Apr 11, 2007
Messages
143
Hi All,

I have sheet which includes an area with several columns where data is entered. Above the data entry area there is text / a header. Immediately below there is a blank line. Below that blank line is the header for the next section.

When opening the file there are initially 20 data entry rows, rows 14 to 33. The blank line is row 34.

Column A is blank and in the data entry area column B is a sequential line number (initially 1 - 20).

I need a macro (triggered by the worksheet change event) which, when the last row is reached will add a row underneath.

So far I've come up with;

Code: 
If Range("B14").End(xlDown).Row = ActiveCell.Row Then
    Rows(ActiveCell.Row).Select
    Selection.Copy
    Rows(ActiveCell.Row + 1).Select
    Selection.Insert Shift:=xlDown
    ActiveCell.Offset(rowOffset:=-1, columnOffset:=2).Activate
End If

Although the first thing that does is correct (adding a new row) it loops and continues adding rows until I stop the macro.

Could anybody please point me towards how I can get it to add only a single row but, when I then move into that new row it will add another below, etc, etc.

Many thanks in advance!!
Richard
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Forgot to mention;

I need to trigger the above from the worksheet_change event.

Many thanks
Richard
 
Upvote 0
Hi,

Is there anybody who is able to help with this query or point me in the right direction, please!? It's getting really quite frustrating, I just cant get a single row to add. :(

Many thanks in advance
Richard
 
Upvote 0
Hopefully this screenshot will help illustrate what I'd like to do;

17peti.jpg


When I start entering data in line 33 I would like to have another line inserted immediately below line 33. Then when I start entering data into this new line 34 I would like another new line inserted below 34, etc, etc.

Many thanks in advance.
Richard

Windows XP Pro
Excel 2003
 
Last edited:
Upvote 0
Try

Code: 
[FONT="Consolas"][SIZE="2"][COLOR="Navy"]If Range("B14").End(xlDown).Row = ActiveCell.Row Then
    ActiveCell.Offset(1, 0).EntireRow.Insert xlShiftDown
    Cells(ActiveCell.Row + 1, 2) = Cells(ActiveCell.Row, 2) + 1
End If[/COLOR][/SIZE][/FONT]
 
Upvote 0
Mohammad Basem said:
Try

Code: 
[FONT="Consolas"][SIZE="2"][COLOR="Navy"]If Range("B14").End(xlDown).Row = ActiveCell.Row Then
    ActiveCell.Offset(1, 0).EntireRow.Insert xlShiftDown
    Cells(ActiveCell.Row + 1, 2) = Cells(ActiveCell.Row, 2) + 1
End If[/COLOR][/SIZE][/FONT]
Click to expand...

Hi Mohammad,

Many thanks for your reply :)

I've just tried it and found that it also loops. I've cleared everything else that was triggered by the worksheet_change event but the problem remains :confused:

Any ideas where I might be going wrong with all this please?

Many thanks in advance
Richard
 
Upvote 0
Now I understood what do mean by loop!

Try this

Code: 
[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("B14").End(xlDown).Row = Target.Row Then
        Application.EnableEvents = False
        Target.Offset(1, 0).EntireRow.Insert xlShiftDown
        Cells(Target.Row + 1, 2) = Cells(Target.Row, 2) + 1
        Application.EnableEvents = True
    End If
End Sub[/COLOR][/SIZE][/FONT]
 
Upvote 0
Mohammad Basem said:
Now I understood what do mean by loop!

Try this

Code: 
[FONT="Arial"][SIZE="2"][COLOR="Navy"]Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("B14").End(xlDown).Row = Target.Row Then
        Application.EnableEvents = False
        Target.Offset(1, 0).EntireRow.Insert xlShiftDown
        Cells(Target.Row + 1, 2) = Cells(Target.Row, 2) + 1
        Application.EnableEvents = True
    End If
End Sub[/COLOR][/SIZE][/FONT]
Click to expand...

Hi Mohammad,

Superb!! Many thanks indeed!! :biggrin:

I combined that with a bit of the original (as I wanted conditional formatting & the sequential record number to continue) and ended up with;

Rich (BB code): 
If Range("B14").End(xlDown).Row = ActiveCell.Row Then
    Application.EnableEvents = False
    Rows(ActiveCell.Row).Select
    Selection.Copy
    Rows(ActiveCell.Row + 1).Select
    Selection.Insert Shift:=xlDown
    ActiveCell.Offset(rowOffset:=0, columnOffset:=2).Activate
    ActiveCell.ClearContents
    ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate
    Application.EnableEvents = True
    Application.CutCopyMode = False
End If

Which seems to do exactly what I want.

Again, many thanks indeed!!

Best regards
Richard
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Insert Custom Rows VBA - Multiple Worksheets
2
Replies
10
Views
443
Peter_SSs
Peter_SSs
theYaniac
  • Solved
I would like to insert a blank row between each new unique identifier in column A.
Replies
3
Views
182
MARK858
MARK858
J
  • Question
Check if any values are already in the established FirstRow
Replies
9
Views
317
NoSparks
N
M
  • Question
How to auto insert a new row after last row, when last row is being filled in.
Replies
0
Views
772
mrgcav
M
I
  • Solved
Cell above is selected when i duplicate row
Replies
2
Views
85
ipbr21054
I

Forum statistics

Threads
1,214,922
Messages
6,122,281
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top