How about looking at each cell and seeing if its the first cell in the
mergearea.

(if the cell isn't merged, then .mergearea will just be that cell.)

I set up a test in F5:F19.
F5:F12 was merged
F13 not merged
F14:f19 was merged

This kind of thing appeared to work ok:

Option Explicit
Sub testme()
Call FunnyClearOfMergedCells(Worksheets("sheet1").Range ("f5:f19"))
Call FunnyClearOfMergedCells(Worksheets("sheet1").Range ("G12:G33"))
End Sub
Sub FunnyClearOfMergedCells(myRng As Range)

Dim iCtr As Long
Dim jCtr As Long

With Worksheets("sheet1")

For iCtr = 1 To myRng.Cells.Count - 1
For jCtr = iCtr + 1 To myRng.Cells.Count
If myRng.Cells(iCtr).MergeArea.Cells(1) _
= myRng.Cells(jCtr).MergeArea.Cells(1) Then
'do nothing in same mergearea
Else
myRng.Cells(iCtr).Cells(1).Value _
= myRng.Cells(jCtr).Value
Exit For
End If
Next jCtr
Next iCtr

With myRng
.Cells(.Cells.Count).MergeArea.Value = ""
End With
End With

End Sub

So you could add that FunnyClearOfMergedCells sub and just call it with the
range specified.



gregork wrote:

What about, after doing the clear contents, I could somehow pull all the
remaining text off the cells and then slap it back on in order. Could you
achieve this with code?

gregork

"Dave Peterson" wrote in message
...
I find that merged cells aren't worth the paper they're printed on.

I do my best to avoid them.



gregork wrote:

Thanks for your help Dave. Unfortunately I can't get this to
work...everything on my sheet goes askew merged cells unmerge and I
formulas
are mucked up. Might be one for the too hard basket.

Cheers
gregork

"Dave Peterson" wrote in message
...
Maybe you could just assign the values from the cells underneath it
and
then
clear that last cell in the range.

Kind of like:

Option Explicit
Sub testme()
Dim myRng As Range
Set myRng = Worksheets("B Sheet").Range("b8:b23")

With myRng
With .Resize(.Rows.Count - 1, 1)
.Value = .Offset(1, 0).Value
End With
.Cells(.Cells.Count).ClearContents
End With
End Sub




gregork wrote:

Thanks for the reply Dave.
I don't want to move the cells in the column only the text.The range
"Worksheets("B Sheet").Range("b8:b23")" is essentially a form with
borders
that I print out. When I use "Delete Shift:=xlUp" to move the text
up
that
is under the text I am clearing I end up with my form in a mess
(borders
out
and formulas I have in cells moved etc.)
I just want to clear the data and then have any text below the data
move
up
to replace it (NB. providing that data is within the range
"Worksheets("B
Sheet").Range("b8:b23")".

Regards
gregork

"Dave Peterson" wrote in message
...
You only want to move the cells under that column:

rng1.Offset(0, 5).Delete Shift:=xlUp
rng1.Offset(0, 0).Delete Shift:=xlUp

if you want to delete the entire row, you only have to do it once.
rng1.Offset(0, 0).EntireRow.Delete

gregork wrote:

I have the following code for clearing an entry on a worksheet:

Dim rng As Range, res As Variant, rng1 As Range
Set rng = Worksheets("B Sheet").Range("b8:b23")

res = Application.Match(CStr(ComboBox1.Text), rng, 0)
If Not IsError(res) Then
Set rng1 = rng(res)
rng1.Offset(0, 5).ClearContents
rng1.Offset(0, 0).ClearContents

What do I need to add to the code to move up the entries in the
cells
below
the entry I am deleting and within the range ?
I have different data in the cells below B23 so I can't have
the
text
in
those cells move up.I have tried Delete Shift:=xlUp and it just
puts
messes
up all the cells below the range ("b8:b23").

Cheers
gregork

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson