How to remove merged cells

[Srikanth]

Hi,

Can any one clarify how to remove merged cells, autofit the data and center
align the contents by having a formula.

Thanks in advance

 

[David Biddulph]

The answer to this, and a number of your other questions, is to start with
Excel help.

 

[Stephen]

Hi,

Can any one clarify how to remove merged cells, autofit the data and
center
align the contents by having a formula.

Thanks in advance

You can't do these things with a formula. Formulas do calculations. To
change merging, autofit and alignment automatically would need a macro.

 

[Srikanth]

Thanks David, I tried with excel help.. but it din't worked.

 

[Srikanth]

Hello Stephen,

I tried with a macro, but I failed as the sheet which i pull from software
does not contain same text/rows/columns every time, it changes from time to
time. For example: In the first report, If B & C columns are merged in one
sheet, In the second report other cells willl be merged. I wanna get rid of
these merged cells and for remaining (i.e., autofit, center alignment and
wrap text) I can run a macro.
Can u suggest me how to use the formula by using macro.

It would be of great use for me.
Thanks in advance...

 

[Stephen]

I am no expert with macros so will not attempt a direct reply. However, you
can remove all merged cells by selecting the whole worksheet (click the box
at the intersection of row numbers and column letters) and using
Format > Cells > Alignment tab
Uncheck "Merge cells" and click OK.

 

[Mike H]

Hi,

Right click the sheet tab, view code and paste this in.

Sub demergency()
With Selection
.MergeCells = False
.HorizontalAlignment = xlCenter
.EntireColumn.AutoFit
End With
End Sub

Select your range and run the code.

Mike

 

[Srikanth]

Stephen,

I tried the below trick, but i could remove only merged cells and could not
delete them... any idea..

Thanks for support.

 

[Srikanth]

Hi Mike,

Can u suggest me how to use this code as I'm not aware of VBA,
and can u please insert one more line in this macro to wrap the cells..That
will be grateful to me..

Excellent reply...

Thanks buddy