Auto-Insert new row in Table?

[Vintage79]

Hello All,

I am using tables in my spreadsheet. When I enter new data, a new row is automatically created in each table - which is great. However, it does not insert a new row in the worksheet - so everything below my table does not get moved down the page. Is there a way of inserting a whole row each time new information is entered, rather than just a new table row?

Thank you!

 

[Dryver14]

Best I could think of., obviously where I have <8 you want to put the number of rows you want to see in the gap +1

code goes in the worksheet not a module

Private Sub Worksheet_Change(ByVal Target As Range)


Dim x As Long
Dim y As Long


    If Target.Column = 1 Then
        x = Cells(1, 1).End(xlDown).Row
        y = Cells(1, 1).End(xlDown).End(xlDown).Row
            If y - x < 8 Then
                Cells(x, 1).Offset(1, 0).EntireRow.Insert
            End If
    End If
        
End Sub

 

[Vintage79]

Thanks for helping Dryver.

I haven't used VBA for so long that I'm out of touch!! I was hoping that there would be a simple setting for this change.

However, I still want to try your suggestion...Do we need to specify what Target.Column is first? I tried this formula but nothing happened. I seem to remember being able to step through code with F8, but that didn't work either. I did enter the code into the sheet, not a module.

To clarify - the first table begins at B4 and ends at N5. There is data above and below the table. A new table row (row6) will be created by entering data in D6.

Thanks again for the help!

 

[crictime]

Thanks, Dryver14 bro. I gotta solution for my problem.

 

[Dryver14]

Just saw your answer.

Where did you get a result from, if it was another site, I'd be keen to learn what was done differently

 

[Vintage79]

Just saw your answer.

Where did you get a result from, if it was another site, I'd be keen to learn what was done differently

That wasn't me

 

[Dryver14]

Oh right.

I am struggling to understand your problem, if the table starts in B then why is info being entered in D?

I could change this to column D IE target column = 4 but you say there is something above the table as well, I need to understand that better

 

[Vintage79]

The table goes from B to N, but the user is not required to enter text in every cell. The cells in columns B and C are auto filled with formulas, so the first place a user will type, when they are putting a new entry into the table, will be in column D.

The first three rows contain the title of the sheet, company logos etc.

So, row 4 contains the table headers, and row 5 is the first entry of data into the table.

 

[Dryver14]

ok give this a try

This assumes there is something in column D below the table if not I need to know where something is.

Private Sub Worksheet_Change(ByVal Target As Range)


Dim x As Long
Dim y As Long


    If Target.Column = 4 Then
        x = Cells(4, 4).End(xlDown).Row
        y = Cells(4, 4).End(xlDown).End(xlDown).Row
            If y - x < 8 Then
                Cells(x, 4).Offset(1, 0).EntireRow.Insert
            End If
    End If
        
End Sub

 

[Vintage79]

Brilliant! Thanks for that - it works perfectly, and doesn't even interfere with displaying the table total row if needed.

I don't know why I am not able to step through the code using F8 and having the locals window open, so that I can see the values that get assigned to x & y. This would help me understand it better, as I'm curious to learn how it works!

Thanks again, you've realy helped!