Solved

How to delete columns from an Excel table using VBA

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associateā€¦
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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 will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

730 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