Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move Text Up
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move Text Up
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" 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? ================================ 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 -- HTH ================================ --- Regards, Norman "gregork" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move Text Up
rng1.Offset(0, 5).Selection.Delete Shift:=xlUp
Think this will work "gregork" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move Text Up
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 "David" wrote in message ... rng1.Offset(0, 5).Selection.Delete Shift:=xlUp Think this will work "gregork" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move Text Up
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 -- Regards, Tom Ogilvy "gregork" wrote in message ... 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 "David" wrote in message ... rng1.Offset(0, 5).Selection.Delete Shift:=xlUp Think this will work "gregork" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move Text Up
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 -- HTH ------- Bob Phillips "gregork" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text - Remove text Item No.99 (First 2 Chars) and move to end | Excel Discussion (Misc queries) | |||
Move text | Excel Discussion (Misc queries) | |||
Move text | Excel Worksheet Functions | |||
Move up text | Excel Programming |