Move Text Up

[gregork]

I am very sorry to have to post this again but after I posted it the first
time I was unable to download any new messages for about three days. I'm not
sure what happened but even now, when I reset the newsgroup and re-download
all the past couple of weeks worth of messages, my original posting has
disappeared..so here we go again:

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 ?
Bear in mind I have different data in the cells below B23 so I can't have
the text in those cells move up.

Greg

 

[Norman Jones]

Hi Greg,

You could find your posts doing a Google Groups search.

Your original post received the following responses from Rob van Gelder and
Bob Phillips:

================================
"Rob van Gelder" <[email protected] wrote in message
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?

================================

<[email protected] wrote in message
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

 

[Guest]

rng1.Offset(0, 5).Selection.Delete Shift:=xlUp
Think this will work

 

[gregork]

Thanks For the replies.
When I use "Delete Shift:=xlUp" it moves the contents from cells below the
range "b8:b23" into the range...which makes a mess of my worksheet.
When I use Rob's code ( rng1.Offset(0, 5).Value = rng1.Offset(1, 5).Value)
....I end up with the row duplicated.

Regards
Greg

 

[Tom Ogilvy]

Sub Tester1()
Dim rng As Range, res As Variant, rng1 As Range
Dim k As Long, lcells As Long
Set rng = Worksheets("Blend Sheet").Range("b8:b23")

num = 26
res = Application.Match(num, rng, 0)
If Not IsError(res) Then
Set rng1 = rng(res)
lcells = 23 - rng1.Row
If lcells > 0 Then
For k = 1 To 2
If k = 2 Then
Set rng1 = rng1.Offset(0, 5)
End If
rng1.Resize(lcells, 1).Value = _
rng1.Offset(1, 0).Resize(lcells, 1).Value
rng1.Offset(lcells, 0).ClearContents
Next
Else
rng1.Offset(0, 5).ClearContents
rng1.ClearContents
End If
End If

End Sub

 

[Bob Phillips]

Gregor,

You could try

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
Range("B22").Offset(0, 5).Insert Shift:=xlDown
Range("B22").Insert Shift:=xlDown
End If