VBA创建表格未选择正确的范围

转换' CurrentRegion '占据第一个单元格上方的行或左侧的列时,返回Excel表格

• 如果代码位于 TrialBalance模板文件 使用 ThisWorkbook 而不是 ActiveWorkbook 。

Sub ConvertTrialBalanceToTable()
    
    With ActiveWorkbook.Worksheets("TrialBalance")
        If .ListObjects.Count = 0 Then
            .ListObjects.Add(xlSrcRange, _
                RefCurrentRegion(.Range("A2")), , xlYes).Name = .Name
        End If
    End With

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns a reference to the range starting with the first cell
'               of a range and ending with the last cell of the first cell's
'               Current Region.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function RefCurrentRegion( _
    ByVal FirstCell As Range) _
As Range
    Const ProcName As String = "RefCurrentRegion"
    On Error GoTo ClearError

    If FirstCell Is Nothing Then Exit Function
    With FirstCell.Cells(1).CurrentRegion
        Set RefCurrentRegion = FirstCell.Resize(.Row + .Rows.Count _
            - FirstCell.Row, .Column + .Columns.Count - FirstCell.Column)
    End With

ProcExit:
    Exit Function
ClearError:
    Debug.Print "'" & ProcName & "' Run-time error '" _
        & Err.Number & "':" & vbLf & "    " & Err.Description
    Resume ProcExit
End Function