Delete multiple columns in a Table

**jomili** · 05-26-2016, 11:33 AM

This code works to delete multiple contiguous columns in a DataRange or in a Table.

ActiveSheet.Range("A:C").EntireColumn.Delete Shift:=xlToLeft

This code works to delete multiple discontiguous columns in a DataRange, but does NOT work on my Table (if I convert it to a range it works fine):

ActiveSheet.Range("A:C,E:G").EntireColumn.Delete Shift:=xlToLeft

How do I need to change the second code to delete multiple discontiguous columns in a Table? The actual columns I want to delete are as shown below, and BA is the last column of my table:

Activesheet.Range("A:C,E:G,J:Q,U:AB,AD:AH,AJ:AR,AU:BA").Delete Shift:=xlToLeft

**biznez** · 05-26-2016, 12:07 PM

try this

Sub jomili ()
Range("AU:BA,AJ:AR,AD:AH,U:AB,J:Q,E:G,A:C").Delete Shift:=xlToLeft
End Sub

**jomili** · 05-26-2016, 12:17 PM

BizNex,
Result is Error code 1004, Delete method of Range class failed.

**JOHN H. DAVIS** · 05-26-2016, 02:12 PM

Try:

Sub jomiliz()
Range("A:C,E:G,J:Q,U:AB,AD:AH,AJ:AR,AU:BA").EntireColumn.Delete
End Sub

**jomili** · 05-26-2016, 03:09 PM

Nope, same error.

**vcoolio** · 05-27-2016, 02:01 AM

Hello Jomili,

The third code in your first post should work. Remove the "Shift:=xlToLeft" part as the default will move all columns to the left anyway.

Cheerio,
vcoolio.

**jomili** · 05-27-2016, 08:17 AM

vcoolio,
My third code is the same as John H. Davis last post. Even though I agree it "should" work, it doesn't work if my range is a Table. It DOES work if my range is simply a range.

**romperstomper** · 05-27-2016, 08:31 AM

You'll need to loop:

Dim rCol As Range
Dim lo As ListObject

Set lo = ActiveSheet.ListObjects(1)

For Each rCol In Intersect(ActiveSheet.Range("A:C,E:G,J:Q,U:AB,AD:AH,AJ:AR,AU:BA"), lo.Range).Columns
    rCol.Delete
Next

**jomili** · 05-27-2016, 08:51 AM

Rorya,

Your code DID delete columns, but it didn't delete the columns I wanted. it deleted columns A, E, G, J, L, N, P, U, W, Y, AA, AD, AF, AH, AL, AN, AP, AR, AU, AW, AY and BA

**romperstomper** · 05-27-2016, 09:12 AM

Apologies - you need to loop backwards, and you can do areas rather than one column at a time:

Dim n As Long
Dim lo As ListObject

Set lo = ActiveSheet.ListObjects(1)

For n = Intersect(ActiveSheet.Range("A:C,E:G,J:Q,U:AB,AD:AH,AJ:AR,AU:BA"), lo.Range).Areas.Count To 1 Step -1
    Intersect(ActiveSheet.Range("A:C,E:G,J:Q,U:AB,AD:AH,AJ:AR,AU:BA"), lo.Range).Areas(n).Delete
Next

**jomili** · 05-27-2016, 10:48 AM

Okay, it works (THANK YOU!), but it raises a lot of questions for me:

What is VBA's idea of an "Area" in this context? Would it be each section of columns in the Range?
So would the "Areas.Count" value be 7 since there are 7 sections of columns to delete?
And so n would be 7, then 6, then 5 and so on, down to 1?

So in the deletion line would it delete AU:BA first, as it's the 7th "Area", then AJ:AR, etc?

**romperstomper** · 05-28-2016, 03:42 AM

Correct. An area is a contiguous range of cells.

**Doc.AElstein** · 05-28-2016, 05:12 AM

As Rory said, in Excel, an Area is a contiguous range of cells . This can be "got at" in VBA throught the Areas Collection Object.

A Range Object is more often than not a contiguous range of cells ( so that is a typical VBA guess as its "Implicit default" thingy

** ). But it does not have to be. It is not defined as such; A Range Object can be a non contiguous range of cells:

A Range Object is a group of cells in a worksheet. The list of the cells of the range is organised in rectangular groups of cells, each one named an Area. ( Can be reffered to by its Item Number, counting, I believe in the order they were added )

I believe when you do something like this ( Where rng is a Range Object )

______rng._____ =

Then that Implicitly defaults to the first Area**

______rng. Areas.Item(1).______ =
_ .. try it .. just add that bit in any code you have, - it does no harm ( well might **** some people off )

You can build up the Areas using the Union Method

Somethings will not "appear" to "work" for you on a Range built with the Union. Or they may only appear to "work" on the first Area you added. That is usually because the code line you write defaults to the first Area, so just applies to that first Area.
I think you usually need to loop through to apply things to all Areas. For example this sort of thing
___For Each rgCell In rgUnion
___ _........_...
___ _...
___Next
is the quickest I believe.

But I think some things will work in "one go" on the Unioned Range Object, as an example:

rgUnion.Interior.Color = vbRed

Rem Ref
http://www.eileenslounge.com/viewtop...181736#p181655

Post #27 http://www.mrexcel.com/forum/excel-q...-column-3.html

Bill Gelatine

**jomili** · 05-31-2016, 08:50 AM

Thanks Doc. Your explanation and links really help explain it well. And thank you Rorya for your perseverance and help in resolving this current issue. Thanks to both of you I've not only solved my issue but have learned useful information for my future projects. Thanks!

**Doc.AElstein** · 05-31-2016, 09:09 AM

Hi jomili

Originally Posted by jomili

....Thanks to both of you I've not only solved my issue but have learned useful information for my future projects. .....

You is welcome. Glad we could help.
Thanks for the Feedback
Alan

EDIT: Apparantly in VBA Help a range can be a 3-D range also, ????
The definition of the Range object in VBA help states that it "represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range".
Someons that knoes a lot about VBA Theory gave some good basic vBA info here:
http://www.mrexcel.com/forum/excel-q...cations-2.html

Delete multiple columns in a Table

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Delete multiple columns in a Table

Re: Delete multiple columns in a Table

Re: Delete multiple columns in a Table

Re: Delete multiple columns in a Table

Re: Delete multiple columns in a Table

Re: Delete multiple columns in a Table

Re: Delete multiple columns in a Table

Re: Delete multiple columns in a Table

Re: Delete multiple columns in a Table

Re: Delete multiple columns in a Table

Re: Delete multiple columns in a Table

Re: Delete multiple columns in a Table

Excel VBA Areas Collection Object Thingy :)

Re: Delete multiple columns in a Table

VBA Range Object. VBA Areas. Areas. Collection of Areas of contiguous cells. Wonk

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] VBA Delete Row If Ciriteria From Multiple Columns are Met

Add and Delete table columns without cells shifting?

[SOLVED] Convert table with multiple header columns into a pivotable table

Delete all rows that do not contain multiple criteria in multiple columns

[SOLVED] Delete multiple empty cells in multiple columns and moving data up, witout Macros

Delete multiple columns by column name no criteria need, just delete them

Delete multiple columns if they fit different criteria

Bookmarks

Bookmarks

Posting Permissions