VBA To Add rows to TABLE based on a cells value

[Utradeshow]

Hi All,

I have a sheet with a TABLE I need a VBA code to add a specified number of rows to the table based on the Value entered in A3 also copying formulas down.

Example: Enter the Value of 20 in Cell A3 and 20 rows added to from row 5 down with formulas also expanding the TABLE SIZE.

TTI Agent Rate Calculator v1.03 - TABLE TEST.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
4	Location	Store Name	Region	Store Type	City	St	Zip*	Address	Zip	Agent Name	City	State	Airport Code	Miles
5									#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
ROLLOUT

Cell Formulas
Range	Formula
I5	I5	=CDXClosestZip(G5,0,AGENTS!$A$2:$A$165)*1
J5	J5	=VLOOKUP(I5,AGENTS!A:B,2,FALSE)
K5	K5	=VLOOKUP(I5,AGENTS!A:C,3,FALSE)
L5	L5	=VLOOKUP(I5,AGENTS!A:D,4,FALSE)
M5	M5	=VLOOKUP(I5,AGENTS!A:E,5,FALSE)
N5	N5	=CDXDistance(G5,I5)

Named Ranges
Name	Refers To	Cells
AGENTS!_FilterDatabase	=AGENTS!$A$1:$R$165	J5:M5

 

[igold]

How about this for a table named "Table15":

Sub ResizeTable()

    Dim rng As Range
    Dim tbl As ListObject
  
    Set tbl = ActiveSheet.ListObjects("Table15")
    Set rng = Range("Table15[#All]").Resize(tbl.Range.Rows.Count + Range("A3").Value, tbl.Range.Columns.Count)
  
    tbl.Resize rng

End Sub

 

[Utradeshow]

How about this for a table named "Table15":

Sub ResizeTable()

    Dim rng As Range
    Dim tbl As ListObject
 
    Set tbl = ActiveSheet.ListObjects("Table15")
    Set rng = Range("Table15[#All]").Resize(tbl.Range.Rows.Count + Range("A3").Value, tbl.Range.Columns.Count)
 
    tbl.Resize rng

End Sub

Yes sir! That works amazing! I took it a step further so when I enter a value in cell A4 it will then add that amount of rows

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim LastRow As Long                           '<- added
    If Intersect(Target, Range("A4")) Is Nothing Or Range("A4") = "" Then Exit Sub
    Dim rng As Range
    Dim tbl As ListObject
 
    Set tbl = ActiveSheet.ListObjects("Table15")
    Set rng = Range("Table15[#All]").Resize(tbl.Range.Rows.Count + Range("A4").Value, tbl.Range.Columns.Count)
 
    tbl.Resize rng

End Sub

 

[igold]

Great, I am glad it worked for you. I see you are in Florida- me too (South on the East coast)