SOLVED

ABOUT ADVANCE TABLE FUNCTIUON

%3CLINGO-SUB%20id%3D%22lingo-sub-918824%22%20slang%3D%22en-US%22%3ERe%3A%20ABOUT%20ADVANCE%20TABLE%20FUNCTIUON%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-918824%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428893%22%20target%3D%22_blank%22%3E%40PRASHANT1497%3C%2FA%3E%26nbsp%3B-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20seems%20to%20work%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F138472i2DA9281031A6C6C9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22vba-dynamic-table-row.gif%22%20title%3D%22vba-dynamic-table-row.gif%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20Dim%20rng%20As%20Range%0A%20%20%20%20Dim%20tbl%20As%20ListObject%0A%20%20%20%20Dim%20rowNum%20As%20Range%0A%20%20%20%20Set%20tbl%20%3D%20ActiveSheet.ListObjects(%22tblDynamic%22)%0A%20%20%20%20Set%20rowNum%20%3D%20Range(%22B3%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20%0A%20%20%20%20If%20Not%20Intersect(Target%2C%20Range(%22B3%22))%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20With%20tbl%0A%20%20%20%20%20%20%20%20%20%20%20%20For%20i%20%3D%20.ListRows.Count%20To%201%20Step%20-1%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20.ListRows(i).Delete%0A%20%20%20%20%20%20%20%20%20%20%20%20Next%20i%0A%20%20%20%20%20%20%20%20End%20With%0A%20%20%20%20%20%20%20%20Set%20rng%20%3D%20Range(%22tblDynamic%5B%23All%5D%22).Resize(tbl.Range.Rows.Count%20%2B%20(rowNum%20-%201)%2C%20tbl.Range.Columns.Count)%0A%20%20%20%20%20%20%20%20tbl.Resize%20rng%0A%20%20%20%20End%20If%0A%20%20%20%20%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-919477%22%20slang%3D%22en-US%22%3ERe%3A%20ABOUT%20ADVANCE%20TABLE%20FUNCTIUON%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-919477%22%20slang%3D%22en-US%22%3EThank%20you%20but%20I%20can't%20understand%20I%20am%20beginner%20can%20you%20please%20show%20formula%20in%20given%20video%20or%20share%20by%20clicking%20image%20or%20share%20video%20including%20process%20of%20writing%20formula%20in%20excel%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-920288%22%20slang%3D%22en-US%22%3ERe%3A%20ABOUT%20ADVANCE%20TABLE%20FUNCTIUON%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-920288%22%20slang%3D%22en-US%22%3EThis%20method%20show%20some%20error%20and%20I%20can't%20decide%20it%20can%20you%20help%20me%20and%20explain%20what%20is%20the%20error%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-922548%22%20slang%3D%22en-US%22%3ERe%3A%20ABOUT%20ADVANCE%20TABLE%20FUNCTIUON%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-922548%22%20slang%3D%22en-US%22%3EHard%20for%20me%20to%20tell%20what%20the%20error%20is.%20I%20just%20recreated%20the%20file%20again%20and%20had%20no%20issues.%20Possibly%20because%20you%20have%20saved%20your%20file%20as%20.xlsx%20instead%20of%20.xlsm%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-922599%22%20slang%3D%22en-US%22%3ERe%3A%20ABOUT%20ADVANCE%20TABLE%20FUNCTIUON%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-922599%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428893%22%20target%3D%22_blank%22%3E%40PRASHANT1497%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYour%20code%20syntax%20is%20incorrect.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20have%20put%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3E%20Set%20tbl%20-%20ActiveSheet.ListObjects(%22tblDynamic%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Einstead%20of%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3E%20Set%20tbl%20%3D%20ActiveSheet.ListObjects(%22tblDynamic%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Enote%20one%20has%20a%20-%20and%20the%20other%20has%20an%20%3D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-918785%22%20slang%3D%22en-US%22%3EABOUT%20ADVANCE%20TABLE%20FUNCTIUON%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-918785%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20create%20table%20which%20do%20not%20have%20fix%20number%20of%20rows.%20If%20I%20input%2010%20in%20specific%20cell%20it%20will%20create%2010%20rows%20in%20table%2Cetc.%3C%2FP%3E%0A%3CP%3EBasically%20I%20want%20to%20create%20table%20which%20has%20number%20of%20rows%20I%20input%20in%201%20specific%20cell.%3C%2FP%3E%0A%3CP%3ECan%20anyone%20help%20me%20to%20write%20formula.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E**Edited%20by%20moderator%20to%20remove%20caps%20lock%20**%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-918785%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-924766%22%20slang%3D%22en-US%22%3ERe%3A%20ABOUT%20ADVANCE%20TABLE%20FUNCTIUON%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-924766%22%20slang%3D%22en-US%22%3EThank%20you.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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 **

6 Replies
Highlighted
Best Response confirmed by PRASHANT1497 (New Contributor)
Solution

@PRASHANT1497 -

 

This seems to work:

vba-dynamic-table-row.gif

 

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
Highlighted
Thank you but I can't understand I am beginner can you please show formula in given video or share by clicking image or share video including process of writing formula in excel
Highlighted
This method show some error and I can't decide it can you help me and explain what is the error
Highlighted
Hard for me to tell what the error is. I just recreated the file again and had no issues. Possibly because you have saved your file as .xlsx instead of .xlsm?

Highlighted

@PRASHANT1497 

 

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 =

Highlighted
Thank you.