Solved

How to delete columns from an Excel table using VBA

Posted on 2014-02-28
5
5,229 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
  • 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 33

Accepted Solution

by:
Norie earned 500 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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 will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

860 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