10

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?

Thanks!

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
Alexander Bell
  • 7,842
  • 3
  • 26
  • 42
Jgonzales
  • 103
  • 1
  • 1
  • 5

1 Answers1

21

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)

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

objTable.TableStyle = "TableStyleMedium2"

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

Hope this will help.

Alexander Bell
  • 7,842
  • 3
  • 26
  • 42
  • Fantastic from both of you - saved me about 2 hours faffing around trying to find a solution. Stole both your idea's added it to a Save to SharePoint piece of code so I can now use the excel sheet in power Automate (Flow) – RobertW081171 Nov 17 '20 at 13:49
  • @RobertW081171 Can you please share how you added code to be able to use excel sheet in Power Automate? – deepDeepSea Jan 11 '21 at 23:16
  • 1
    Please add your definition for xlSrcRange and Selection – Golden Lion Apr 07 '21 at 21:34