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

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

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

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

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

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