Convert Range to a table-CodePudding

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. Could someone please help me with this?

Thanks,


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

CodePudding user response：

Can You try :

Sub TryMe()
    ActiveSheet.ListObjects.Add(xlSrcRange, Range(ThisWorkbook.Sheets("Database").Range("a1").End(xlDown), ThisWorkbook.Sheets("Database").Range("a1").End(xlToRight)), , xlYes).Name = "Table1"
End Sub

CodePudding user response：

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