How to build a dynamic matrix table with user defined no.of rows and no.of columns

Are you trying to select rows and columns of a table which has rows and columns dynamic in nature?

Hello,

by "Table" you mean a ListObject? Usually a table re-sizes automatically when you fill an adjacent cell.

santosh,

Its against the forum rules to create duplicate threads. I have merged them for you now.

Santhosh,

You can use OFFSET function to create a dynamic named range, which I believe can cater your issues.

<first_cell_ref> : is the first cell from where the table actually starts. Basically the top-left-most cell. Gives the idea from where to start
<fully_filled_range_rows> : is the column having continuous filled rows. It basically gets the row count.
<fully_filled_range_cols> : is the row having continuous filled columns. It basically gets the column count.

=OFFSET(<first_cell_ref>, 0 , 0, COUNTA (<fully_filled_range_rows>), COUNTA(<fully_filled_range_cols>))

This gives you a dynamic table that keeps growing both ways whenever a new column or rows are added.

And to let the user specify the dimensions he wants use sth like in this file: CreateTable.xlsm

contains:

Option Explicit

Sub CreateTable()
    Const DEFAULT_ROW As Long = 12
    Const DEFAULT_COL As Long = 12
    
    Dim vntInput1, vntInput2
    
    On Error GoTo CreateTable_ErrorHandler
    Application.ScreenUpdating = False

    'get number of rows:
    Do
        vntInput1 = Application.InputBox("Enter the number of Rows.", "Create Table", DEFAULT_ROW, , , , , 1)
        If TypeName(vntInput1) = "Boolean" Then
            Exit Do
        ElseIf CLng(vntInput1) <> vntInput1 Then
            Select Case MsgBox("A Number without decimals is required, do you want to re-enter or use the default value of " & _
                    DEFAULT_ROW & "?" & String(2, vbLf) & "OK: Re-Enter" & vbLf & "No: Use default " & _
                    DEFAULT_ROW & vbLf & "Cancel: Exit program", vbQuestion + vbYesNoCancel, "Create Table")
                    
                Case vbNo
                    vntInput1 = DEFAULT_ROW
                Case vbCancel
                    GoTo CreateTable_Proc_Exit
            End Select
        Else
            Exit Do
        End If
    Loop
    
    'get number of cols:
    Do
        vntInput2 = Application.InputBox("Enter the number of Columns.", "Create Table", DEFAULT_COL, , , , , 1)
        If TypeName(vntInput2) = "Boolean" Then
            Exit Do
        ElseIf CLng(vntInput2) <> vntInput2 Then
            Select Case MsgBox("A Number without decimals is required, do you want to re-enter or use the default value of " & _
                    DEFAULT_COL & "?" & String(2, vbLf) & "OK: Re-Enter" & vbLf & "No: Use default " & _
                    DEFAULT_COL & vbLf & "Cancel: Exit program", vbQuestion + vbYesNoCancel, "Create Table")
                    
                Case vbNo
                    vntInput2 = DEFAULT_COL
                Case vbCancel
                    GoTo CreateTable_Proc_Exit
            End Select
        Else
            Exit Do
        End If
    Loop
    
    'make table:
    ActiveSheet.Range("A1").Resize(CLng(vntInput1), CLng(vntInput2)).Value = "x"
    

CreateTable_Proc_Exit:
    Application.ScreenUpdating = True
    Exit Sub
CreateTable_ErrorHandler:
    MsgBox "Error: " & Err.Number & " (" & Err.Description & ") in Sub 'CreateTable' of Module 'Module1'.", vbOKOnly + vbCritical, "Error"
    Resume CreateTable_Proc_Exit
    
End Sub

Hi,

please check: Dynamic TABLE WITH DATA CONDITONS .xlsm

Check: 636FB000.xlsm

1. Colors changed
2. When user changes a row "heading" in column 1 of the table, the same text will be pasted to the appropriate column heading and vice versa
3. Apart from the resize button, the table will automatically resize and apply color if the user enters test into 1 row below the last in column 1 OR 1 column to the right next to the last column

Hi Tehnexus,

THis document is getting bigger and bigger . Confusing a lot . I have attached the document . THe thing is everything should be dynamic . I can do that normally but i Cant do it dynamically . Please check the document u will understand the logic.

Regards
Santhosh