Clear then move text up

[gregork]

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]

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]

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]

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]

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]

I find that merged cells aren't worth the paper they're printed on.

I do my best to avoid them.

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

 

[gregork]

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]

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.

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]

ps. I really hate merged cells!

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

 

[gregork]

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