excelvba

Convert Range to a table


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

Solution

  • 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