How to delete columns from an Excel table using VBA

mamuscia
mamuscia used Ask the Experts™
on
I have a named table on an Excel worksheet.  I want to remove columns from that table using VBA.  I recorded a macro and it gives me this code:
Range("T_201402[[#Headers],[Delivery Exceptions]:[VM RU?]]").Select
Selection.ListObject.ListColumns(58).Delete
Selection.ListObject.ListColumns(58).Delete

Open in new window


Is this the only way to delete columns?  What does the ListColumns(58) mean?
I also want to avoid SELECTing the columns first.

I am using Excel 2010.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
listcolumns(58) is the column selected for deletion (ie: what you would get if you typed the formula "=column()" into a cell in that column)

So the Range part is indicating  your "selection" - it seem you selected two columns side by side - then with the 'listobject' (ie: the table) you requested a "Delete" of each column one after the other.

If you always know where the columns are you can be more direct:-
Sub c_delete()
With Range("b:c")
.Delete
End Sub

Author

Commented:
is there any way I can delete the columns using their names?  or, can i get the column number by referencing their names?  what if the table changes and the columns are no long at 58?
Analyst Assistant
Commented:
ListColumns(58) is the 58th column in the table, the name can be used instead of the index.

What's the name of the table and column?

Here's a simple example that deletes the 'Field3' colum from the table named 'Table1'.
Dim lst As ListObject

Set lst = ActiveSheet.ListObjects("Table1")

lst.ListColumns("Field3").Delete

Open in new window

Sub c_delete()
Dim rng As Range
Set rng = Application.Range("table1[Column1]")
With rng.Select
rng.Delete
End With
End Sub

Author

Commented:
Fantastic!  Works perfectly.

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