Convert Range to a table

Question

I have an Excel spreadsheet with the tabs "Analysis" and "Database". I want to create a button in Analysis that when clicked will convert the Database tab into a table. The Database isn't static and different users are always adding data.

I have the code below but it fails at the ".parent..." line of code.


Sub Convert_Table()
 
 With ThisWorkbook.Sheets("Database").Range("a1")

    .Parent.ListObjects.Add(xlSrcRange, ThisWorkbook.Sheets("Database").Range(.End(xlDown), .End(xlToRight)), , xlYes).Name = "Table1"

  End With

End Sub

Could you share why you need this more than once? Is somebody writing outside of the table i.e. creating empty lines? Is the name of the current table also `Table1`? — VBasic2008, Nov 08 '21 at 17:01
HI. So the userform is basically an on-going survey/evaluation where new data keeps getting added (or edited or deleted). So multiple people are filling out the form which writes the data to the database tab. — Mike, Nov 08 '21 at 18:09

Siddharth Rout · Accepted Answer · 2021-11-12T11:09:53.067

ThisWorkbook.Sheets("Database").Range("a1").Parent is the Sheets("Database"). Simplify your code.

I would do this slightly different.

I will find the last row and last column to make my range and then create the table. xlDown and xlToRight are not reliable if there are blank cells in between.

Is this what you are trying (UNTESTED)? I have commented the code but if you still have a problem understanding it, simply post a comment below.

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim rng As Range
    Dim tbl As ListObject

    '~~> This is your worksheet
    Set ws = ThisWorkbook.Sheets("Database")
    
    With ws
        '~~> Unlist the previously created table
        For Each tbl In .ListObjects
            tbl.Unlist
        Next tbl

        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            '~~> Find last row
            lastRow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
            
            '~~> Find last column
            lastCol = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByColumns, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Column
            
            '~~> Set your rnage
            Set rng = .Range(.Cells(1, 1), .Cells(lastRow, lastCol))
            
            '~~> Create the table
            .ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "Table1"
        End If
    End With
End Sub

Hi. Thanks for the help. When I run the code I get an error at the line " .ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "Table1" and the error message is RUn Tim 1004: A table can't overlap another table. — Mike, Nov 08 '21 at 16:29
AHA! two questions for you. **1.** Is there already another table in the worksheet? **2.** Will you have multiple tables in the worksheet? — Siddharth Rout, Nov 08 '21 at 16:31
1. Is there already another table in the worksheet? a. No, not in the “Database” tab b. I have three tabs in my Excel document i. The User Form tab where users input data ii. The Database tab which populates from the input from the User Form (this is what I am trying to convert to a table) iii. The Analysis tab which will eventually be the dashboard 2. Will you have multiple tables in the worksheet? a. Not within the same tab, no. — Mike, Nov 08 '21 at 16:38
In that case the code that i gave for database sheet will help but if there is already another table there then you will get the error. Let me add couple of lines which will delete the exisitng tables in that worksheet. seems like there was already a previous table there — Siddharth Rout, Nov 08 '21 at 16:45

Convert Range to a table

1 Answers1