VBA to find and delete columns.

[saravanan1981]

Hi,
I have sheet "Working". In this I have a column with Heading New Sheet1, New Sheet2, New Sheet3, New Sheet4, New Sheet5 and New Sheet6.

I need a macro to find above Heading New Sheet1, New Sheet2, New Sheet3, New Sheet4, New Sheet5 and New Sheet6 in Row number 1 and if it exist then delete those columns.

And find last filled column in the same sheet and add new 6 columns with same heading New Sheet1, New Sheet2, New Sheet3, New Sheet4, New Sheet5 and New Sheet6

The Column heading format should be in Red font.bold and backgroung yellow colour

[Richard Buttrey]

So you don't actually want to delete the column labels, it appears you just want to clear anything that's in those columns. In which case

Please Login or Register  to view this content.

[saravanan1981]

Somehow.... But not exactly.

Because these columns may anywhere in the the sheet, Between any columns or first last or in a MID. Also wanted clear contents in existing columns. Hence Wanted to find and delete these columns and insert new columns (one after one) after a last column,

[Akuini]

Try this:

Please Login or Register  to view this content.

[saravanan1981]

hi,
Please check the attachment the code is not running

[Rick Rothstein]

Does this macro do what you want...

Please Login or Register  to view this content.

[saravanan1981]

Hi, I have given Column Heading Starting with "New Sheet" just for an example.

Actuall I have 10 columns with below headings. And more can be added / deleted in future. Can you provide your accordingly.


No_Annualised_Claims
No_Annualised_Claims_With_IBNR
Annualised_Claim_Amount
Annualised_Claim_Amount_With_IBNR
Age_Band_1
Age_Band_2
Age_Band_3
Age_Band_4
Age_Band_5
Amount_Band

[jindon]

Please Login or Register  to view this content.

[saravanan1981]

Dear Jindon,
I tried it on sample file. It is not deleting all existing column with same heading.
at final result only 10 columns to be there after running the code. that too it start from the last column

[jindon]

Add/remove the item in myList.

Please Login or Register  to view this content.

[saravanan1981]

thats perfect

[Rick Rothstein]

No_Annualised_Claims
No_Annualised_Claims_With_IBNR
Annualised_Claim_Amount
Annualised_Claim_Amount_With_IBNR
Age_Band_1
Age_Band_2
Age_Band_3
Age_Band_4
Age_Band_5
Amount_Band

Question... are the above headers the only headers that have red bold font in a yellow filled cell? If so, here is another macro that you can consider...

Please Login or Register  to view this content.

Note: If those headers are the only header with yellow fill, then you can remove the two lines of code I highlighted in red above.