Merged Cells Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jim - 31 Jul 2008 17:01 GMT

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

Reply to this Message

RyanH - 31 Jul 2008 17:11 GMT

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.

Signature

Cheers,
Ryan

> if your Range.EntireRow contains merged cells how can you test for that and
> if true, unmerge the cells?
[quoted text clipped - 9 lines]
> End If
> End Function

Reply to this Message

RyanH - 31 Jul 2008 17:15 GMT

Correction!
Sub UnMergeRange()

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

End Sub

Signature

Cheers,
Ryan

> 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.
[quoted text clipped - 30 lines]
> > End If
> > End Function

Reply to this Message

Jim - 31 Jul 2008 17:30 GMT

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

> Correction!
> Sub UnMergeRange()
[quoted text clipped - 38 lines]
> > > End If
> > > End Function

Reply to this Message

RyanH - 31 Jul 2008 18:10 GMT

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"

Signature

Cheers,
Ryan

> 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
[quoted text clipped - 54 lines]
> > > > End If
> > > > End Function

Reply to this Message