Hi Dave
Thanks for your help. I have come up with a way around my problem its not
ideal but it works. I have used code to duplicate the entries I make on to
the form to another area on the worksheet (an area where I can use Delete
Shift:=xlUp without causing any problems) After I have deleted an entry I
use code to clear my form area and then copy the text from the new area.

Cheers
gregork

"Dave Peterson" wrote in message
...
ps. I really hate merged cells!

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