• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1671
  • Last Modified:


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

  • 2
  • 2
1 Solution
Rory ArchibaldCommented:
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.
KrukanensAuthor Commented:
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.
Rory ArchibaldCommented:
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.
KrukanensAuthor Commented:
Thank You 'rorya'.

It works.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now