Extending Excel Table

[tiredofit]

Workbook A contains one worksheet with two Excel tables.

Table1's range is A1 to E5.

Table 2's range is A11 to E15.


If the activecell is E5 and I press the tab key, another row is automatically added to Table1 (as expected).

Furthermore, Table2 now starts on row 12 (because it has been shifted down a row).

I believe this is the normal behaviour of Excel tables.


However, in Workbook B, with the same setup, pressing the tab key whilst in cell E5 DID add another row to Table1 BUT no extra row was inserted between Tables1 and 2, meaning now there are only 4 empty rows between them.

After repeating several times, Tables1 and 2 were on consecutive rows. Now when I press the tab key, Table2 shifted down.

How can I replicate Workbook A's behaviour?

Thanks

 

[Peter_SSs]

I believe this is the normal behaviour of Excel tables.

I don't think so. I think normal is that it only moves the bottom table down when it needs to.

How can I replicate Workbook A's behaviour?

See if this work-around would be any use, assuming it works for you as it works for me.

1. Select A6:A10 & enter a space character in all 5 cells with Ctrl+Enter. This will extend Table1 down to meet Table2
2. With your cursor within Table1 go to the Table Design ribbon tab & choose 'Resize Table'
3. Change the data range address back to $A$1:$E$5 -> OK. A6:A10 will still have the space characters but not be part of Table1
4. Now try the Tab from cell E5

 

[Alex Blakenburg]

You only need to have something in "any" cell in the row directly below the table.
So you can simplify the steps to:

1. In A6 enter a space
2. Ctrl+Z
   Note: The table expanding is a separate step. It will enter the space then as follow up step expand the table, so ctrl+Z undoes the last step being the table expansion

A big proviso is: This will only work if the Table(s) under the expanding table have either the same number of column or less columns.
If it has more columns the Table will no longer automatically expand when you have put something in the row under it.
Without having something in that row it will expand but not push down what is under it, so will expand until it hits the table below it.

 

[tiredofit]

Thanks to the responses, they both worked.

This has actually put me off using Tables even more! The fact there are "hidden" spaces means it can easily be "messed up" by the end user, if they're not aware the spaces are provided for a reason.

 

[Peter_SSs]

This has actually put me off using Tables even more!

Fair enough and certainly your choice - but of course if you want two sets of separate 'tables' of data above each other and they are not formal tables then they will certainly run into each other as more data is added to the top one & I'm sure that could also be "messed up" by the end user.

 

[tiredofit]

Fair enough and certainly your choice - but of course if you want two sets of separate 'tables' of data above each other and they are not formal tables then they will certainly run into each other as more data is added to the top one & I'm sure that could also be "messed up" by the end user.

I prefer creating "robust" tools, so in the past, have restricted users from altering the number of rows / columns on the worksheet directly.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Selection.CountLarge Mod 1048576 = 0 Or Selection.CountLarge Mod 16384 = 0 Then
        With Application
            .EnableEvents = False
            .Undo
            EnableEvents = True
        End With
    End If

End Sub

Instead, I have created custom menus for those occasions.

 

[Peter_SSs]

I prefer creating "robust" tools, so in the past, have restricted users from altering the number of rows / columns on the worksheet directly.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Selection.CountLarge Mod 1048576 = 0 Or Selection.CountLarge Mod 16384 = 0 Then
        With Application
            .EnableEvents = False
            .Undo
            EnableEvents = True
        End With
    End If

End Sub

Instead, I have created custom menus for those occasions.

I presume that that the EnableEvents = True should have been preceded by a "." but in any case how would that help if you had a set of data in A1:E5 and a set of data in A11:E15 that you did not want to collide/overwrite if more data was added to the A1:E5 range?

I don't see that restricting rows/columns on the sheet has anything to do with adding more data to the table in A1:E5

 

[tiredofit]

I presume that that the EnableEvents = True should have been preceded by a "." but in any case how would that help if you had a set of data in A1:E5 and a set of data in A11:E15 that you did not want to collide/overwrite if more data was added to the A1:E5 range?

I don't see that restricting rows/columns on the sheet has anything to do with adding more data to the table in A1:E5

Yes, it was a typo.

Wth Tables, if the user tabs on the last cell in the Table, another row would be added and if the space trick was not used, Table 1 would "run into, though not clash" Table 2.

If I didn't have Tables, then if the user wants to insert more rows to extend the data in the range A1 to E5, (with my code added), they would use my custom menu.

 

[Peter_SSs]

If I didn't have Tables, then if the user wants to insert more rows to extend the data in the range A1 to E5, (with my code added), they would use my custom menu.

I just couldn't see (& still can't) that the code in post #6 had anything to do with that. However, if you have code to deal with inserting more rows to extend a table it shouldn't matter whether you use formal tables or not as far as I can see. BTW, I am not trying to particularly talk you into or out of using them.

Sounds like you are happy to use vba so if you were wanting to use formal tables it would be possible to have code to ensure the bottom table moved down when rows were added to the top table.

 

[HighAndWilder]

Thanks to the responses, they both worked.

This has actually put me off using Tables even more! The fact there are "hidden" spaces means it can easily be "messed up" by the end user, if they're not aware the spaces are provided for a reason.

If you are worried about users messing things up in this way then only have one table on each sheet.

Users will always find ways to mess things up.

This code will insert a new row beneath a table when the user enters a value into the first cell in the last row of a table.

It is fairly basic without any data validation but it seems to do the job.

Suggestions for improvements welcome.

Option Explicit

Dim varPrevious As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tbl As ListObject
Dim rng As Range
Dim objActiveTable As ListObject
Dim strCheckIfTable As String

  If Target.CountLarge > 1 Then
    Exit Sub
  End If
 
  ' Test To See If The Active Cell Is Within A Table
  On Error Resume Next
  strCheckIfTable = Target.ListObject.Name
  On Error GoTo 0
 
  ' If not then abort.
  If strCheckIfTable = "" Then
    Exit Sub
  End If
 
  Set objActiveTable = ListObjects(strCheckIfTable)
 
  ' Only proceed to insert a row beneath the table if :
  ' 1. The active cell is in column one of the table.
  '   NOTE : This can be changed to any column.
  ' 2. The active cell is on the last row of the table.
  ' 3. Upon selection of the table cell it was empty.
  '
  With objActiveTable
   
    If Not Intersect(objActiveTable.DataBodyRange.Columns(1), Target) Is Nothing And _
      (.HeaderRowRange.Row + .ListRows.Count = Target.Row) And _
        Len(Trim(varPrevious)) = 0 Then
   
      Target.Offset(2, 0).EntireRow.Insert
  
   End If
 
  End With
 
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  varPrevious = Target.Value

End Sub