Delete Multiple Columns with specific column headings using a Macro.

[e4excel]

Dear Forum,

I need a little help in deleting a Few Columns which come in my Daily Dump...

Ex: I have almost 59-60 Column Size Range of Data in which I need to delete a few columns as these are less relevant for my daily report.

Manually Deleting them is tedious as this is a Daily Report and so if something can be done with a VBA macro this will a boon for me.

There's also one catch as the Column Headings sometimes come differently like so please add an OR to the condition if possible.

Ex: - I need to delete the 2 columns whose Column Headings are "Hub Code" and "Log PPT" or sometimes this may come as "HubCode" and "LogPPT"..

So the columns with these column headings get deleted and the other column takes its place.

Regards
e4excel

[MarvinP]

Hi e4excel,

See if this will work for you:

Please Login or Register  to view this content.

[Decar]

Argh. I was too slow again ^^


Anyways. Here is what I did

Please Login or Register  to view this content.

[MarvinP]

Sorry I was too quick.

There is a problem when deleting rows or columns and counting UP. If two rows or columns next to each other need to be deleted then your type of code will miss the second one. This is why it is better to start at the last one and move up or to the left. This way the counter from top to bottom works.

I hope this makes sense and helps you have a better scheme next time.

[Decar]

Thank you!

Very good to know.

[e4excel]

Dear MarvinP,

For some strange Im getting an error in this line "LastCol = Cells(1, Column.Count).End(xlToLeft).Column" as Column is not a defined variable then just with fluke I tried it with Columns..it worked but still it does not move both the columns to the left..

Please Login or Register  to view this content.

Added this line from Decars code.. "Shift:=xlToLeft"

but still though both columns got deleted , one of the columns just remained blank..


Regards
e4excel

[xlbiznes]

Hi,

Try this :

Please Login or Register  to view this content.

[e4excel]

Thanks xlbzines and sorry Marvin I just realised that there was a small piece of code which was actually inserting a blank column instead of Cutinng and Inserting the column.

Just a small question based on the same thing how do i Cut specific columns & insert the columns before or after specific columns?

Thanks everyone for the help...

[MarvinP]

My Bad,

It should have been Columns.Count (with an S) in the line of:
LastCol = Cells(1, Column.Count).End(xlToLeft).Column

In the immediate window type "?Columns.Count" and press enter. It will show the max number of columns in your worksheet. It is different in 2003 vs 2007 (vs 2010??) and above Excel.

[MarvinP]

Hi e4excel,

To do this extra step here is what I'd do....

I'd record a macro to insert a column and then cut and copy another column into the blank one I'd inserted (If I understand what you are trying to do). Then I'd stop recording and look at the code the record produced. Then you patch it up with code that uses Cells(row, col) type of notation.....

I can't tell you how many times I've learned VBA by recording a macro...

I hope that helps you figure out how to insert columns and paste others into the blank one you just created..