Dynamic Excel table rows

[ExcelNewbie2020]

Sirs,

I have an excel table created wherein the values of each cell are a result of an excel function. Every day, I need to adjust the table border because there is changes from the rows.
Would it be possible that the table rows would adjust dynamically based on the cell with values?

I hope I explain it well. Please see the attached.

Many Thanks,

 

[igold]

You have not given that much information to work with. This code will look down the first column of your table and resize the table to the last row that has data in that first column. The code will not change the number of columns in the table. I have used "Table1" as the name of the table. Please change the code to match your table name where indicated. Please test on a backup copy of your data...

Sub ResizeTable()

    Dim fr As Variant, spl As Variant
    Dim tbl As ListObject
    Dim rng As Range
    Dim adr As String, cl As String
    Dim cn As Long, lr As Long, fr2 As Long

    Application.ScreenUpdating = False
    Set tbl = ActiveSheet.ListObjects("Table1")     '*** Change Table Name Here
    Set rng = Range("Table1[#All]").Resize(2, tbl.ListColumns.Count)      '*** Change Table Name Here
    ActiveSheet.ListObjects("Table1").Resize rng    '*** Change Table Name Here
    adr = tbl.Range.Address
    spl = Split(adr, "$")
    cl = spl(1)
    fr = Split(spl(2), ":")
    fr2 = CLng(fr(0))
    cn = Range(cl & 1).Column
    lr = Cells(Rows.Count, cn).End(xlUp).Row
    Set rng = Range("Table1[#All]").Resize(lr + 1 - fr2, tbl.ListColumns.Count)  '*** Change Table Name Here
    ActiveSheet.ListObjects("Table1").Resize rng             '*** Change Table Name Here
    Application.ScreenUpdating = True
  
End Sub

 

[ExcelNewbie2020]

You have not given that much information to work with. This code will look down the first column of your table and resize the table to the last row that has data in that first column. The code will not change the number of columns in the table. I have used "Table1" as the name of the table. Please change the code to match your table name where indicated. Please test on a backup copy of your data...

Sub ResizeTable()

    Dim fr As Variant, spl As Variant
    Dim tbl As ListObject
    Dim rng As Range
    Dim adr As String, cl As String
    Dim cn As Long, lr As Long, fr2 As Long

    Application.ScreenUpdating = False
    Set tbl = ActiveSheet.ListObjects("Table1")     '*** Change Table Name Here
    Set rng = Range("Table1[#All]").Resize(2, tbl.ListColumns.Count)      '*** Change Table Name Here
    ActiveSheet.ListObjects("Table1").Resize rng    '*** Change Table Name Here
    adr = tbl.Range.Address
    spl = Split(adr, "$")
    cl = spl(1)
    fr = Split(spl(2), ":")
    fr2 = CLng(fr(0))
    cn = Range(cl & 1).Column
    lr = Cells(Rows.Count, cn).End(xlUp).Row
    Set rng = Range("Table1[#All]").Resize(lr + 1 - fr2, tbl.ListColumns.Count)  '*** Change Table Name Here
    ActiveSheet.ListObjects("Table1").Resize rng             '*** Change Table Name Here
    Application.ScreenUpdating = True
 
End Sub

this does work, I used "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" so everytime a row is move the table will auto adjust.. thanks a lot.. really appreciated..

 

[Peter_SSs]

If interested, you could also give this a try.
I have assumed that the DataBodyRange will always have at least one row with data.

Sub ResizeTbl()
  With ActiveSheet.ListObjects("Table1")
    .Resize .Range.Resize(.Range.Find("*", , xlValues, , xlByRows, xlPrevious).Row - .Range.Row + 1)
  End With
End Sub

 

[ExcelNewbie2020]

You have not given that much information to work with. This code will look down the first column of your table and resize the table to the last row that has data in that first column. The code will not change the number of columns in the table. I have used "Table1" as the name of the table. Please change the code to match your table name where indicated. Please test on a backup copy of your data...

Sub ResizeTable()

    Dim fr As Variant, spl As Variant
    Dim tbl As ListObject
    Dim rng As Range
    Dim adr As String, cl As String
    Dim cn As Long, lr As Long, fr2 As Long

    Application.ScreenUpdating = False
    Set tbl = ActiveSheet.ListObjects("Table1")     '*** Change Table Name Here
    Set rng = Range("Table1[#All]").Resize(2, tbl.ListColumns.Count)      '*** Change Table Name Here
    ActiveSheet.ListObjects("Table1").Resize rng    '*** Change Table Name Here
    adr = tbl.Range.Address
    spl = Split(adr, "$")
    cl = spl(1)
    fr = Split(spl(2), ":")
    fr2 = CLng(fr(0))
    cn = Range(cl & 1).Column
    lr = Cells(Rows.Count, cn).End(xlUp).Row
    Set rng = Range("Table1[#All]").Resize(lr + 1 - fr2, tbl.ListColumns.Count)  '*** Change Table Name Here
    ActiveSheet.ListObjects("Table1").Resize rng             '*** Change Table Name Here
    Application.ScreenUpdating = True
 
End Sub

Sir, i forgot to add on my explanation that the sequence in the column K (1 to 15 and so on...) depends the cell with values. so if the cell (rows) with values expand the sequence should continue. Maybe there is a code that will fit.. thank you again..

 

[ExcelNewbie2020]

If interested, you could also give this a try.
I have assumed that the DataBodyRange will always have at least one row with data.

Sub ResizeTbl()
  With ActiveSheet.ListObjects("Table1")
    .Resize .Range.Resize(.Range.Find("*", , xlValues, , xlByRows, xlPrevious).Row - .Range.Row + 1)
  End With
End Sub

this also works, can we possibly add a code for the sequence in column K (1 to 15 and so on) it supposed to follow the cell (rows) with values. thank you

 

[Peter_SSs]

Is this what you mean?

Sub ResizeTbl_v2()
  With ActiveSheet.ListObjects("Table1")
    .Resize .Range.Resize(.Range.Find("*", , xlValues, , xlByRows, xlPrevious).Row - .Range.Row + 1)
    .DataBodyRange.Columns(1).Value = Evaluate("row(1:" & .Range.Rows.Count - 1 & ")")
  End With
End Sub

BTW, instead of converting this to run from the Selection_Change event, wouldn't it make more sense to run it from the Worksheet_Change event?
Unless you have some other code connected to Selection_Change, changing the selection in the worksheet should not affect how many rows the table contains.
On the other hand, if you are actually adding or deleting table rows, that would trigger the Worksheet_Change evnt

 

[igold]

You're welcome. We were both happy to help. Thanks for the feedback.

Are you all squared away now.

@Peter_SSs , I was unclear of how the OP's data was appearing when his worksheet changed. I tried to write a code that would cover this kind of situation where the new data fell outside of the current DataBodyRange...

Table Max column Value.xlsm
	E	F	G	H	I	J	K	L	M
9	col 1	col 2	col 3	col 4	col 5	col 6	col 7	col 8	col 9
10	1	1	1	1	1	1	1	1	1
11	1	1	1	1	1	1	1	1	1
12	1	1	1	1	1	1	1	1	1
13	1	1	1	1	1	1	1	1	1
14	1	1	1	1	1	1	1	1	1
15
16
17
18	2	2	2	2	2	2	2	2	2
19	2	2	2	2	2	2	2	2	2
20	2	2	2	2	2	2	2	2	2
21	2	2	2	2	2	2	2	2	2
22	2	2	2	2	2	2	2	2	2
23	2	2	2	2	2	2	2	2	2
Sheet1

 

[Peter_SSs]

@Peter_SSs , I was unclear of how the OP's data was appearing when his worksheet changed. I tried to write a code that would cover this kind of situation where the new data fell outside of the current DataBodyRange...

Fair enough. In fact I took the approach I did with the same sort of layout in mind but my thinking was that those extra separate rows below should not be included in the table.
We'll hopefully find out in due course.

 

[igold]

My thinking was that if the data was already in the table than the only thing he would need was code to contract the table, but if the table had more rows than data, I could not find the last row as it would pick up the last row of the table. And he had pointers on both sides of his arrow...