Push down rows when rows added to table ..... how?????

[spydey]

I have a Table (Table1) in one of my sheets. Let's say it goes from A2:D45.

From A50:D60, I have some additional cells (range) being used for other items.

When I go to A46 and input something, the table increases by one row, now including the data input in A46. This is what I want it to do.

However, the empty space between the last row of the table and A50, has now decreased.

Eventually my table will extend down past A50, which will cause issues with the data I have in A50:D60.

How can I go about making it so that no matter how many rows my table extends. ... I will always have 5 rows of space between my table's last row, and the next used range (i.e. A50:D60)?

Essentially I want to ensure that things get "pushed down" each time a my table extends by a new row.

I was thinking it would have to be in VBA ..... something like: whenever the table is extended, count the number of blank rows between the last row of the table and the first row (down) which is not blank, if less than 5, add X number of rows between to always have 5 rows between the last row of the table and the first not blank row.

Thoughts? Ideas?

-Spydey

 

[spydey]

I figured it out!!!

It really was quite simple once I thought about.

I simply changed

r.Cells(2, 1).Resize(WorksheetFunction.CountA(r)).EntireRow.Insert

to

r.Range(Cells(2, 1),Cells(2,11).Resize(WorksheetFunction.CountA(r)).Insert xlShiftDown

Works like a charm ...... as far as I can tell.

Thanks @igold and @Akuini for your time, help, and patience!!

-Spydey

 

[igold]

Great. I am glad you figured it out. Thanks for the feedback!

 

[spydey]

Quick Question, for the code that currently exists, if I wanted to change it from (currently): data input in the row after the table's last row, the table gets resized to include the row after the table's last row

to: Data input in table's last row adds a row below the table and resizes the table to include it

what would that look like?

-Spydey

 

[Akuini]

Try this:
Note:
This will add a blank row as the last row of the table.
I'm not sure why you need to format the cell border in "Worksheet_Change" event, so I don't include that part in the code below:

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Worksheet_Change([COLOR=Royalblue]ByVal[/COLOR] Target [COLOR=Royalblue]As[/COLOR] Range)
[COLOR=Royalblue]With[/COLOR] ActiveSheet.ListObjects([COLOR=brown]"Table1"[/COLOR])
    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, .ListRows(.Range.Rows.Count - [COLOR=crimson]1[/COLOR]).Range) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        Application.EnableEvents = [COLOR=Royalblue]False[/COLOR]
        [COLOR=Royalblue]Dim[/COLOR] r [COLOR=Royalblue]As[/COLOR] Range
        .Resize .Range.Resize(.Range.Rows.Count + [COLOR=crimson]1[/COLOR], .Range.Columns.Count)
        
        [COLOR=Royalblue]Set[/COLOR] r = .Range.Offset(.Range.Rows.Count).Resize([COLOR=crimson]5[/COLOR], [COLOR=crimson]1[/COLOR])
        [COLOR=Royalblue]If[/COLOR] WorksheetFunction.CountA(r) > [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR]
            r.Cells([COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR]).Resize(WorksheetFunction.CountA(r), .Range.Columns.Count).Insert xlDown
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
        Application.EnableEvents = [COLOR=Royalblue]True[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[spydey]

@Akuini

Thank you very much. I was trying something like that but couldn't seem to get it right. I appreciate your time and help.

Question for you: why the " - 1" in:

If Not Intersect(Target, .ListRows(.Range.Rows.Count - 1).Range) Is Nothing Then .......

-Spydey