?
Solved

How to delete columns from an Excel table using VBA

Posted on 2014-02-28
5
Medium Priority
?
6,504 Views
Last Modified: 2014-02-28
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.
0
Comment
Question by:mamuscia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 19

Expert Comment

by:regmigrant
ID: 39894953
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
0
 

Author Comment

by:mamuscia
ID: 39894968
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?
0
 
LVL 34

Accepted Solution

by:
Norie earned 2000 total points
ID: 39895052
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

0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39895077
Sub c_delete()
Dim rng As Range
Set rng = Application.Range("table1[Column1]")
With rng.Select
rng.Delete
End With
End Sub
0
 

Author Closing Comment

by:mamuscia
ID: 39895117
Fantastic!  Works perfectly.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

765 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