Clear then move text up
 

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

Clear then move text up
 

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

Clear then move text up
 

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

Clear then move text up
 

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

Clear then move text up
 

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

Clear then move text up
 

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

Clear then move text up
 

What about, after doing the clear contents, I could somehow pull all the
remaining text off the cells and then slap it back on in order. Could you
achieve this with code?

gregork

"Dave Peterson" wrote in message
...
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

Clear then move text up
 

How about looking at each cell and seeing if its the first cell in the
mergearea.

(if the cell isn't merged, then .mergearea will just be that cell.)

I set up a test in F5:F19.
F5:F12 was merged
F13 not merged
F14:f19 was merged

This kind of thing appeared to work ok:

Option Explicit
Sub testme()
Call FunnyClearOfMergedCells(Worksheets("sheet1").Range ("f5:f19"))
Call FunnyClearOfMergedCells(Worksheets("sheet1").Range ("G12:G33"))
End Sub
Sub FunnyClearOfMergedCells(myRng As Range)

Dim iCtr As Long
Dim jCtr As Long

With Worksheets("sheet1")

For iCtr = 1 To myRng.Cells.Count - 1
For jCtr = iCtr + 1 To myRng.Cells.Count
If myRng.Cells(iCtr).MergeArea.Cells(1) _
= myRng.Cells(jCtr).MergeArea.Cells(1) Then
'do nothing in same mergearea
Else
myRng.Cells(iCtr).Cells(1).Value _
= myRng.Cells(jCtr).Value
Exit For
End If
Next jCtr
Next iCtr

With myRng
.Cells(.Cells.Count).MergeArea.Value = ""
End With
End With

End Sub

So you could add that FunnyClearOfMergedCells sub and just call it with the
range specified.



gregork wrote:

What about, after doing the clear contents, I could somehow pull all the
remaining text off the cells and then slap it back on in order. Could you
achieve this with code?

gregork

"Dave Peterson" wrote in message
...
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

--

Dave Peterson

Clear then move text up
 

ps. I really hate merged cells!

gregork wrote:

What about, after doing the clear contents, I could somehow pull all the
remaining text off the cells and then slap it back on in order. Could you
achieve this with code?

gregork

"Dave Peterson" wrote in message
...
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

--

Dave Peterson

Clear then move text up
 

Hi Dave
Thanks for your help. I have come up with a way around my problem its not
ideal but it works. I have used code to duplicate the entries I make on to
the form to another area on the worksheet (an area where I can use Delete
Shift:=xlUp without causing any problems) After I have deleted an entry I
use code to clear my form area and then copy the text from the new area.

Cheers
gregork

"Dave Peterson" wrote in message
...
ps. I really hate merged cells!

gregork wrote:

What about, after doing the clear contents, I could somehow pull all the
remaining text off the cells and then slap it back on in order. Could
you
achieve this with code?

gregork

"Dave Peterson" wrote in message
...
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

--

Dave Peterson