Excel vba to create table

PeterBaileyUk
PeterBaileyUk used Ask the Experts™
on
I have a sub that populates a worksheet from an access query. How do I those rows  into a table using vba?

I found this but it wants a range and the range will change everytime its updated.

When I manually created the table i only needed to click in 1 cell of the data and excel knew the range automatically, so I need to do that action automatically.

 Sub CreateTable()
    Sheets("Pivot SMMT Raw").ListObjects.Add(xlSrcRange, Range("$A$1:$N$16"), , xlYes).Name = _
        "Table1"
        'No go in 2003
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"
End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012
Commented:
Here's an example that creates the table automatically, from a range that starts in A1.

Sub makeTable()
Dim wkb As Workbook
Dim wks As Worksheet
Dim rng As Range
Dim r As Range
Dim lastCol As Long
Dim lastRow As Long

    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets("DataSource")
    
    lastCol = wks.Cells(1, wks.Columns.Count).End(xlToLeft).Column
    lastRow = wks.Cells(wks.Rows.Count, 1).End(xlUp).Row
    
    'get range of table
    Set rng = wks.Range("A1", wks.Cells(lastRow, lastCol))
    
    'convert to table list object
    On Error Resume Next
    wks.ListObjects("Table1").Unlist 'in case a table was there, previously
    On Error GoTo 0
    wks.ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "Table1"
End Sub

Open in new window


See attached.

Dave
makeTableFromRange-r1.xlsm
NorieAnalyst Assistant
Commented:
This will create a table based on the sheet's used range.
With Sheets("Pivot SMMT Raw")
       .ListObjects.Add(xlSrcRange, .UsedRange , xlYes).Name ="Table1"
        'No go in 2003
        .ListObjects("Table1").TableStyle = "TableStyleLight2"
End With

Open in new window

Most Valuable Expert 2012
Top Expert 2012

Commented:
imnorie - I don't have a lot of luck with UsedRange.  Seems that Excel has a way of messing up the UsedRange in my models/workbooks, so I tend to not use it.

Dave
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

NorieAnalyst Assistant

Commented:
Dave

I've found that to but it's normally been only when dealing with data that isn't well organised, disjointed, unstructured etc

I'm assuming that if this data is from Access it will be well organised/structured and that there isn't any data on the worksheet.

Could be wrong of course, the data might have been dropped right in the middle of a load of other stuff.
Most Valuable Expert 2012
Top Expert 2012
Commented:
Agreed -

I've just found that UsedRange identifies any font changes on the sheet as part of the "used range", so I just don't mess with it.

If the data coming from Access is refreshed, I hesitate to say that UsedRange would correct itself to the revised UsedRange (e.g., if next query refresh has less rows), but haven't tested that particular approach to see whether it behaves or not.

Dave

Author

Commented:
I hope you didnt mind that I shared the points as both made a contribution.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial