Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
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) | |||
Using VBA to move from one text box to another | Excel Discussion (Misc queries) | |||
Move text | Excel Discussion (Misc queries) | |||
Move text | Excel Worksheet Functions |