Oct 18 2019 08:10 AM - last edited on Oct 21 2019 09:15 AM by Allen
Oct 18 2019 08:10 AM - last edited on Oct 21 2019 09:15 AM by Allen
I want to create table which do not have fix number of rows. If I input 10 in specific cell it will create 10 rows in table,etc.
Basically I want to create table which has number of rows I input in 1 specific cell.
Can anyone help me to write formula.
**Edited by moderator to remove caps lock **
Oct 18 2019 09:17 AM
Solution
This seems to work:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim tbl As ListObject
Dim rowNum As Range
Set tbl = ActiveSheet.ListObjects("tblDynamic")
Set rowNum = Range("B3")
Application.ScreenUpdating = False
If Not Intersect(Target, Range("B3")) Is Nothing Then
With tbl
For i = .ListRows.Count To 1 Step -1
.ListRows(i).Delete
Next i
End With
Set rng = Range("tblDynamic[#All]").Resize(tbl.Range.Rows.Count + (rowNum - 1), tbl.Range.Columns.Count)
tbl.Resize rng
End If
Application.ScreenUpdating = True
End Sub
Oct 19 2019 01:49 AM
Oct 20 2019 01:27 AM
Oct 21 2019 08:49 AM
Oct 21 2019 09:14 AM
Your code syntax is incorrect.
You have put
Set tbl - ActiveSheet.ListObjects("tblDynamic")
instead of
Set tbl = ActiveSheet.ListObjects("tblDynamic")
note one has a - and the other has an =
Oct 18 2019 09:17 AM
Solution
This seems to work:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim tbl As ListObject
Dim rowNum As Range
Set tbl = ActiveSheet.ListObjects("tblDynamic")
Set rowNum = Range("B3")
Application.ScreenUpdating = False
If Not Intersect(Target, Range("B3")) Is Nothing Then
With tbl
For i = .ListRows.Count To 1 Step -1
.ListRows(i).Delete
Next i
End With
Set rng = Range("tblDynamic[#All]").Resize(tbl.Range.Rows.Count + (rowNum - 1), tbl.Range.Columns.Count)
tbl.Resize rng
End If
Application.ScreenUpdating = True
End Sub