• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automatically Insert Row into Table

G

gumbles

New Member
Hello all,


As I understand it when you format cells as a table,if you put data in the row below it will automatically add it to and format it as a table.


Is it possible that aswell as formatting as a table it can insert a row into the worksheet?


This is because I have data in the cells below dont want it to be overwritten and im not sure how many rows I will need in the future.


Cheers,


Gumbles.
 
so youre asking if you have a range of cells 'formatted as a table', if you insert a row anywhere inbetween the first row and the last row of this 'table' will that newly inserted row be a part of and formatted as the rest of the table?


if i'm understanding your question then yes, it should.
 
Hi Jason,


Thats not exactly what im after,


When you fill a table and press enter and start entering data it formats that new line as a table. What I am trying to get is if you fill the table it automatically inserts a row at the bottom and formats it as a table. not just add a row at the bottom.


This is because I have data underneath this table and without automatically inserting a new row it would eventually overwrite the data underneath.


Gumbles
 
Hi, gumbles!


Let us assume that you have a table at columns A:C from rows 2 thru 3 with titles on row 1, and that from row 5 you have another data that you don't want to be overwritten and you'd like to be shifted one row below each time you add a new line (row 4 in the example) to the table.


You can handle it with a little of VBA code in the Worksheet_Change event.


Give a look at this file:

https://dl.dropbox.com/u/60558749/Automatically%20Insert%20Row%20into%20Table%20%28for%20gumbles%20at%20chandoo.org%29.xlsm


This is the involved code:

-----

[pre]
Code: 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' constants
Const ksNonOverwritableDataStart = "NonOverwritableDataStart"
' declarations
Dim rng As Range
' start
Set rng = Range(ksNonOverwritableDataStart)
If Application.Intersect(Target, rng.Offset(-1, 0)) Is Nothing Then GoTo Worksheet_Change_Exit
Application.EnableEvents = False
' process
With rng
.EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
.Offset(-1, 0).Select
End With
' end
Application.EnableEvents = True
Worksheet_Change_Exit:
Set rng = Nothing
End Sub
[/pre]
-----


Just advise if any issue.


Regards!
 
@b(ut)ob(ut)hc

Hi, old dog!

Good afternoon for you, my friend.

Nice to read you too and I hope you'd be breathing the hot and warm airs of the Caribean seas at this time, but I guess I'm wrong.

Yes, back again after a few days, very few in my opinion, just 35, I was wondering why not 70, but my bank account said "No!".

Regards!
 
Hi, gumbles!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
Good day gumbles


A way to achieve without VBA (with the greatest respect to sirJB7)


Insert a table row or column

1. Do one of the following:

To insert one or more table rows, select one or more table rows above which you want to insert one or more blank table rows.

If you select a cell or range in the last row, you can also insert a row above or below that row.

To insert one or more table columns, select one or more table columns to the left of which you want to insert one or more blank table columns.

If you select a cell or range in the last column, you can also insert a column to the left or to the right of that column.

2. On the Home tab, in the Cells group, click the arrow next to Insert.

3. Do one of the following:

To insert table rows, click Insert Table Rows Above.

To insert a table row below the last row, click Insert Table Row Below.

To insert table columns, click Insert Table Columns to the Left.

To insert a table column to the right of the last column, click Insert Table Column to the Right.

You can also right-click one or more table rows or table columns, point to Insert on the shortcut menu, and then select what you want to do from the list of options. Or you can right-click one or more cells in a table row or table column, point to Insert, and then click Table Rows Above or Table Columns to the Left.
 
@b(ut)ob(ut)hc

Hi!

It's always a pleasure to read you, very old man. Join me with a Carlsberg, if you don't mind, of course. An instructive pleasure in this case.

Regards!
 
@Bobhc,


Thankyou very much for the alternative. I'm actually working on another workbook where im using the methods you have mentioned.


And I'd recommend a bottle of Laphroaig ;)


Regards,


Gumbles
 
@gumbles

Hi!

So do I... just for tasting purposes... ehhh... sorry, for testing purposes.

Regards!
 
@ SirJB7


I really like the script you've provided, but I need it to go one step further, I have 2 tables on same sheet that may need expansion. I can use your formula with the named ranges on either one, but I'm not able to wrap my head around how to do both. My bosses really want the expense report to be a single page, but the people filling out the report sometimes need more space.


My expense report, and sad attempt at trying to modify your script can be found:


https://www.dropbox.com/s/3bv2ax3b3jzknfm/Expense%20Report%20V2.xlsm


and I always appreciate the help of the ninja squads here at Chandoo.org
 
@The Doctor

Hi!


I'd usually write what follows in answer to a post out of topic, but in this case it sounds much more rude than what's intended to. If it hadn't passed three months since last post maybe it'd have been the proper place but considering that time...


Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. More indeed when it's such and old topic. If needed you could add a reference in your new one.


Perhaps you'd want to read the green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).


Regards!


PS: In the meanwhile I'm giving a look at your orange colored uploaded file.
 
You must log in or register to reply here.
Back
Top