Delete multiple columns in a Table

[jomili]

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

Please Login or Register  to view this content.

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):

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

[biznez]

try this

Please Login or Register  to view this content.

[jomili]

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

[JOHN H. DAVIS]

Try:

Please Login or Register  to view this content.

[jomili]

Nope, same error.

[vcoolio]

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]

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.

[rorya]

You'll need to loop:

Please Login or Register  to view this content.

[jomili]

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

[rorya]

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

Please Login or Register  to view this content.

[jomili]

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?

[rorya]

Correct. An area is a contiguous range of cells.

[Doc.AElstein]

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]

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]

Hi 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