Deleting columns

[BigKev]

The attached workbook is part of a presentation I create for a weekly meeting. It is an Excel 2003 format workbook which is taken by my VB .NET program that adds some Excel 2007 formatting and then saves it in Excel 2007 format for subsequent adding to a PowerPoint presentation.

Part of the formatting my program attempts is to remove columns F and G (The Promo columns) depending on the value of a checkbox.

As you can see, the cells A1 and A2 are merged across the headings. If I try programmatically (either via .NET Interop or via a macro (DeletePromo in the workbook)) to remove the promo columns while these cells are merged then Excel deletes all the data in the sheet. If I unmerge the cells then the delete goes OK but only sometimes.

If I manually select the columns then Delete then everything works just fine whether or not the cells are merged.

Am I missing something here? I have Googled in vain.

As always, any and all help will be greatly appreciated.

Regards,
Kevin Bell

[HansV]

Try this version of the macro:

Code: Select all

Sub DeletePromo()
  Range("F1:G1").EntireColumn.Delete
End Sub

By the way, I'd avoid merged cells. It's better to set 'Center across selection' as horizontal alignment.

[BigKev]

Thanks Hans, it works perfectly.

Strange that the DeletePromo macro was recorded and gave the error though.

I made a note of your suggestion about merging cells.

Many thanks,
Kevin Bell

[HansV]

Merged cells don't play nicely with VBA. Interactively, selecting columns F and G works correctly, but in VBA, the merged cells cause columns A:M to be selected instead of just F and G.

As you can see from my version, it's not necessary to select a range in order to delete it.

If you unmerge A1:M1 and A2:M2 and set their horizontal alignment to 'Center across selection', the problem with selecting columns F and G in VBA doesn't occur.