使用VBA在Excel工作表中创建表

问题描述：

我下面有这段代码，它将自动选择一个范围. 有谁知道我如何添加代码以创建到所选范围的表?

I have this code below that will auto select a range. Does anyone know how I can add code to create a table to the selected range?

谢谢！

Sub DynamicRange()
'Best used when first column has value on last row and first row has a value in the last column

Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Set sht = Worksheets("Sheet1")
Set StartCell = Range("D9")

'Find Last Row and Column
  LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
  LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column

'Select Range
  sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select

End Sub

答

使用以下Excel VBA代码段添加与所选Range相对应的Table对象:

Use the following Excel VBA code snippet to add the Table object corresponding to selected Range:

Dim objTable As ListObject
Set objTable = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes)

您还可以将可选样式应用于添加的Table对象，如下所示:

You can also apply optional styling to the added Table object like shown below:

objTable.TableStyle = "TableStyleMedium2"

MSDN上提供了更多详细信息: https://msdn.microsoft. com/en-us/library/office/ff823155.aspx

More details available at MSDN: https://msdn.microsoft.com/en-us/library/office/ff823155.aspx

希望这会有所帮助.

使用VBA在Excel工作表中创建表

相关推荐