Posted on 2012-09-18
Last Modified: 2012-09-19
Hello Experts,

I have Excel 2010 vba code that creates ListObject with ListObject.Add(xlSrcRange, ...) and then fills data from vba recordset.

The problem is:
1. After creating ListObject AUTOFILTER appears in the heading row. How can I disable it.
2. After creating ListObject default column names appears as Column1, Column2, ... but problem is that it CHANGES my column width. Is it possible to "freeze" somehow column width so creating ListObject do not affect them.
3. How can I give the STYLE NAME (one from available in Excel) to ListObject
4. And finally how can I DELETE ListObject

Question by:Krukanens
    LVL 85

    Expert Comment

    by:Rory Archibald
    1. Listobject.Showautofilter = False
    2. You could try Listobject.Showheaders = False
    3. Listobject.TableStyle = "TableStyleMedium2" for example
    4. If you don't want the data use the Delete method of the listobject; if you do, use the Unlist method.

    Author Comment

    Thanks 'rorya'

    I deal with:
    1. .ListObjects("Table1").ShowAutoFilter = False
    2. .ListObjects("Table1").ShowHeaders = True
    3. .ListObjects("Table1").TableStyle = "TableStyleMedium2"
    4. .ListObjects("Table1").Delete

    But problem remains with column width.

    I found that I can do something with columns using:
    5. .ListObjects("Table1").ListColumns(5)....

    So I would be happy if I can:
    a.) set column width for column 5 in ListObjects("Table1")
    b.) set font to bold for column 5

    But I can not find how to do it.
    LVL 85

    Accepted Solution

    activesheet.listobjects("Table1").listcolumns(5).range.entirecolumn.columnwidth = 25

    Open in new window

    activesheet.listobjects("Table1").listcolumns(5).range.font.bold = true

    Open in new window

    if you don't want to bold the headers then use databodyrange instead of range.

    Author Closing Comment

    Thank You 'rorya'.

    It works.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now