Solved

How to delete columns from an Excel table using VBA

Posted on 2014-02-28
5
4,733 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Boolean help 6 29
Excel Save As Status Box will not go away 6 20
Google Sheets Youth Baseball Round Robin Scheduling 5 18
Excel 17 16
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

863 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

26 Experts available now in Live!

Get 1:1 Help Now