Deleting columns with merged cells in a macro

[Frank.Egelund.Jensen]

I have a worksheet where columns A:D are merged. I want to delete columns B:D and this works perfect when I manually select column B and then C and finally D.

I have recorded this, but the macro deletes all the 4 columns.

I have also tried to unmerge the columns and this also works fine manually, but again when recorded and done as a macro it doen't work.

Anybody have the explaination and solution to this

 

[hiker95]

Frank.Egelund.Jensen,

The below macro will first check in the cells in the active worksheet if Range("A1:D1") are merged. If they are the macro will do what you require.

Sample raw data in the active worksheet (sorry, the below screenshot does not show cells/rows A1:D9 merged):

Excel 2007
	A	B	C	D	E
1	A1				E1
2	A2				E2
3	A3				E3
4	A4				E4
5	A5				E5
6	A6				E6
7	A7				E7
8	A8				E8
9	A9				E9
10	A10	B10	C10	D10	E10
11
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E
1	A1	E1
2	A2	E2
3	A3	E3
4	A4	E4
5	A5	E5
6	A6	E6
7	A7	E7
8	A8	E8
9	A9	E9
10	A10	E10
11
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub UnMergedABCD_DeleteBCD()
' hiker95, 08/15/2013
' http://www.mrexcel.com/forum/excel-questions/720348-deleting-columns-merged-cells-macro.html
With ActiveSheet
  If .Range(.Cells(1, 1), .Cells(1, 4)).MergeCells = True Then
    .Columns("A:D").MergeCells = False
    .Columns("B:D").Delete
  End If
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the UnMergedABCD_DeleteBCD macro.