if your Range.EntireRow contains merged cells how can you test for that and
if true, unmerge the cells?

i have something like this but not working.

Function MergeSub( )
Dim rng as Range
Set rngFound = Range("A1:P40")

If rng.MergeCells = True Then
rngFound.EntireRow.MergeCells = False
End If
End Function

I'm not sure if there is a way to test that a row has merged cells. If you
want to unmerge cells in a range just use one of these procedures.

Sub UnMergeRange()

' unmerge all cells in the used range
Sheets("Sheet1").UsedRange.UnMerge

End Sub

or

Sub UnMergeRange()

' unmerge all cells in the range you specified
Sheets("Sheet1").Range("A1:P40".UnMerge

End Sub

Is this what you wanted? Hope this helps.
--
Cheers,
Ryan


"Jim" wrote:

if your Range.EntireRow contains merged cells how can you test for that and
if true, unmerge the cells?

i have something like this but not working.

Function MergeSub( )
Dim rng as Range
Set rngFound = Range("A1:P40")

If rng.MergeCells = True Then
rngFound.EntireRow.MergeCells = False
End If
End Function

Correction!
Sub UnMergeRange()

' unmerge all cells in the range you specified
Sheets("Sheet1").Range("A1:P40").UnMerge

End Sub

--
Cheers,
Ryan


"RyanH" wrote:

I'm not sure if there is a way to test that a row has merged cells. If you
want to unmerge cells in a range just use one of these procedures.

Sub UnMergeRange()

' unmerge all cells in the used range
Sheets("Sheet1").UsedRange.UnMerge

End Sub

or

Sub UnMergeRange()

' unmerge all cells in the range you specified
Sheets("Sheet1").Range("A1:P40".UnMerge

End Sub

Is this what you wanted? Hope this helps.
--
Cheers,
Ryan


"Jim" wrote:

if your Range.EntireRow contains merged cells how can you test for that and
if true, unmerge the cells?

i have something like this but not working.

Function MergeSub( )
Dim rng as Range
Set rngFound = Range("A1:P40")

If rng.MergeCells = True Then
rngFound.EntireRow.MergeCells = False
End If
End Function

I guess the rngFound is kinda confusing what i actually need to do. I have a
program that looks in column "I" for a value and if the value matches what
its looking for it copies row-paste to sheet(2)-Deletes Entire row (in
sheet(1)). I am getting an error that says "Cannot changed part of a merged
cell" so im guess im saying i need to unmerge just 1 row at a time if it has
any part of it merged with other cells

Function MergeSub( )
Dim rng as Range
Set rngFound = Range("A1:P40")

If rng.MergeCells = True Then
rngFound.EntireRow.MergeCells = False
End If
End Function

"RyanH" wrote:

Correction!
Sub UnMergeRange()

' unmerge all cells in the range you specified
Sheets("Sheet1").Range("A1:P40").UnMerge

End Sub

--
Cheers,
Ryan


"RyanH" wrote:

I'm not sure if there is a way to test that a row has merged cells. If you
want to unmerge cells in a range just use one of these procedures.

Sub UnMergeRange()

' unmerge all cells in the used range
Sheets("Sheet1").UsedRange.UnMerge

End Sub

or

Sub UnMergeRange()

' unmerge all cells in the range you specified
Sheets("Sheet1").Range("A1:P40".UnMerge

End Sub

Is this what you wanted? Hope this helps.
--
Cheers,
Ryan


"Jim" wrote:

if your Range.EntireRow contains merged cells how can you test for that and
if true, unmerge the cells?

i have something like this but not working.

Function MergeSub( )
Dim rng as Range
Set rngFound = Range("A1:P40")

If rng.MergeCells = True Then
rngFound.EntireRow.MergeCells = False
End If
End Function

In the future I would recommend not to merge cells becuase as you can see it
is a thorn in the side when it comes to coding. I would just use this line
before you copy the entirerow. I'm will assume you are using a For...Loop,
thus the row address is i. If not, replace i with your row number. Post
your code, it would help.

Rows(i).UnMerge

Hope this helps! If so, click "Yes"
--
Cheers,
Ryan


"Jim" wrote:

I guess the rngFound is kinda confusing what i actually need to do. I have a
program that looks in column "I" for a value and if the value matches what
its looking for it copies row-paste to sheet(2)-Deletes Entire row (in
sheet(1)). I am getting an error that says "Cannot changed part of a merged
cell" so im guess im saying i need to unmerge just 1 row at a time if it has
any part of it merged with other cells

Function MergeSub( )
Dim rng as Range
Set rngFound = Range("A1:P40")

If rng.MergeCells = True Then
rngFound.EntireRow.MergeCells = False
End If
End Function

"RyanH" wrote:

Correction!
Sub UnMergeRange()

' unmerge all cells in the range you specified
Sheets("Sheet1").Range("A1:P40").UnMerge

End Sub

--
Cheers,
Ryan


"RyanH" wrote:

I'm not sure if there is a way to test that a row has merged cells. If you
want to unmerge cells in a range just use one of these procedures.

Sub UnMergeRange()

' unmerge all cells in the used range
Sheets("Sheet1").UsedRange.UnMerge

End Sub

or

Sub UnMergeRange()

' unmerge all cells in the range you specified
Sheets("Sheet1").Range("A1:P40".UnMerge

End Sub

Is this what you wanted? Hope this helps.
--
Cheers,
Ryan


"Jim" wrote:

if your Range.EntireRow contains merged cells how can you test for that and
if true, unmerge the cells?

i have something like this but not working.

Function MergeSub( )
Dim rng as Range
Set rngFound = Range("A1:P40")

If rng.MergeCells = True Then
rngFound.EntireRow.MergeCells = False
End If
End Function