I need a function that would loop through all the cells in the used range of a single sheet and convert any merged cells to "xlCenterAcrossSelection" for the same range as the cells that were originally merged

For example, if the range "B2:J2" was merged, convert range "B2:J2" to centered across the selection

My code (below) unmerges the cells but then fails to convert them to "center across"

Dim rngCell As Rang
For Each rngCell In ActiveSheet.UsedRang
If rngCell.MergeCells = True The
Selection.UnMerg
Selection.HorizontalAlignment = xlCenterAcrossSelection <this line fails without any error
End I
Next rngCel

Could someone please correct my code and/or show me example code that would accomplish what I'm trying to do

TIA

try
Dim rngCell As Range
For Each rngCell In ActiveSheet.UsedRange
If rngCell.MergeCells = True Then
rngcell.UnMerge
rngcell.HorizontalAlignment = xlCenterAcrossSelection <this line
fails without any errors
End If
Next rngCell


--
Don Guillett
SalesAid Software

"stone" wrote in message
...
I need a function that would loop through all the cells in the used range
of a single sheet and convert any merged cells to "xlCenterAcrossSelection"
for the same range as the cells that were originally merged.

For example, if the range "B2:J2" was merged, convert range "B2:J2" to
centered across the selection.

My code (below) unmerges the cells but then fails to convert them to
"center across":

Dim rngCell As Range
For Each rngCell In ActiveSheet.UsedRange
If rngCell.MergeCells = True Then
Selection.UnMerge
Selection.HorizontalAlignment = xlCenterAcrossSelection <this
line fails without any errors
End If
Next rngCell

Could someone please correct my code and/or show me example code that
would accomplish what I'm trying to do?

TIA

Stone,

That is because you are iterating through the range using a range object,
named rngCell, but then you try and reference it via Selection, which is not
where rngCell is pointing to, and could be anywhere.

Try this

Dim rngCell As Range
For Each rngCell In ActiveSheet.UsedRange
If rngCell.MergeCells = True Then
rngCell.UnMerge
rngCell.HorizontalAlignment = xlCenterAcrossSelection
End If
Next rngCell


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"stone" wrote in message
...
I need a function that would loop through all the cells in the used range
of a single sheet and convert any merged cells to "xlCenterAcrossSelection"
for the same range as the cells that were originally merged.

For example, if the range "B2:J2" was merged, convert range "B2:J2" to
centered across the selection.

My code (below) unmerges the cells but then fails to convert them to
"center across":

Dim rngCell As Range
For Each rngCell In ActiveSheet.UsedRange
If rngCell.MergeCells = True Then
Selection.UnMerge
Selection.HorizontalAlignment = xlCenterAcrossSelection <this
line fails without any errors
End If
Next rngCell

Could someone please correct my code and/or show me example code that
would accomplish what I'm trying to do?

TIA