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

 

[tiredofit]

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.

There's lots more I need to look into about Tables before deciding whether I should use them or not but so far, I'm reluctant.

 

[tiredofit]

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

Thanks.

I usually try to be as acommodating as possible and if the user wants multples tables on a worksheet, I'll try to find the best solution.

 

[Peter_SSs]

.. so far, I'm reluctant.

I am a bit the same. They have some good points but sometimes they can be awkward. Just depends on the particular circumstance for me.