Move up text
 

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

Dim rng As Range, res As Variant, rng1 As Range
Set rng = Worksheets("Blend 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).Value = ClearContents
rng1.Offset(0, 0).Value = 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 ?

Greg

Move up text
 

Does your code work?
ClearContents is a method, but it looks like you've made a variable called
ClearContents.

The usual syntax is rng1.Offset(0, 5).ClearContents

To move the values from the row after, eg.
rng1.Offset(0, 5).Value = rng1.Offset(1, 5).Value


PS. What are you Blending?


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"gregork" wrote in message
...
I have the following code for deleting an entry on a worksheet:

Dim rng As Range, res As Variant, rng1 As Range
Set rng = Worksheets("Blend 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).Value = ClearContents
rng1.Offset(0, 0).Value = 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 ?

Greg

Move up text
 

Greg,

I think this is what you mean

Dim rng As Range, res As Variant, rng1 As Range
Set rng = Worksheets("Blend 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).Delete Shift:=xlUp
rng1.Offset(0, 0).Delete Shift:=xlUp


--

HTH

RP
(remove nothere from the email address if mailing direct)


"gregork" wrote in message
...
I have the following code for deleting an entry on a worksheet:

Dim rng As Range, res As Variant, rng1 As Range
Set rng = Worksheets("Blend 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).Value = ClearContents
rng1.Offset(0, 0).Value = 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 ?

Greg