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

spydey

Active Member
Joined
Sep 19, 2017
Messages
314
Office Version
  1. 2013
Platform
  1. Windows
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
 
Last edited:
I figured it out!!!

It really was quite simple once I thought about.

I simply changed

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

to

Code:
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
 
Upvote 0

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Great. I am glad you figured it out. Thanks for the feedback!
 
Upvote 0
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
 
Upvote 0
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:


Code:
[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]
 
Upvote 0
@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
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

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