Prev: sheet2 = sheet1
Next: Variant Array Copy, Array row contains Object
From: ordnance1 on 11 Apr 2010 19:17
I have cells A1, B1, C1, D1 and E1 each contains a name. What I need to do
is, if the contents of say cell C1 is deleted the I need the contents of
cells D1 and E1 to move up. It is critical that that the name remain in the
same order (there by precluding a normal sort). So this would have to work
regardless of which cell(s) are cleared.
From: JLatham on 12 Apr 2010 01:56
Please recheck the way you've described the problem. You say names are in
A1, B1, C1, D1 and E1. Then you say when C1 is deleted you want D1 and E1 to
"move up". There is no "up" from row 1. And since if only C1 were deleted,
D1 and E1 would still have names in them, so you couldn't move D2:D# and
E2:E# up without it overwriting the current contents of D1 and E1.

Also, give us some before and after examples and we may be able to help you.

"ordnance1" wrote:

> I have cells A1, B1, C1, D1 and E1 each contains a name. What I need to do
> is, if the contents of say cell C1 is deleted the I need the contents of
> cells D1 and E1 to move up. It is critical that that the name remain in the
> same order (there by precluding a normal sort). So this would have to work
> regardless of which cell(s) are cleared.
From: ordnance1 on 12 Apr 2010 18:16
Sorry you are of course correct. What I intended to say was cells A1, A2, A3,
A4 and A5.

A1 Pat
A2 Ron
A3 Dave
A4 Linda
A5 Sam

If I remove the name from A3 then everything below A3 the names below should
move up. I due to the structure of the worksheet I cannot just delete the cell
A1 Pat
A2 Ron
A3 Linda
A4 Sam
A5


"JLatham" wrote:

> Please recheck the way you've described the problem. You say names are in
> A1, B1, C1, D1 and E1. Then you say when C1 is deleted you want D1 and E1 to
> "move up". There is no "up" from row 1. And since if only C1 were deleted,
> D1 and E1 would still have names in them, so you couldn't move D2:D# and
> E2:E# up without it overwriting the current contents of D1 and E1.
>
> Also, give us some before and after examples and we may be able to help you.
>
> "ordnance1" wrote:
>
> > I have cells A1, B1, C1, D1 and E1 each contains a name. What I need to do
> > is, if the contents of say cell C1 is deleted the I need the contents of
> > cells D1 and E1 to move up. It is critical that that the name remain in the
> > same order (there by precluding a normal sort). So this would have to work
> > regardless of which cell(s) are cleared.
From: J_Knowles on 12 Apr 2010 21:11
See if this is what you're after.

Sub ClearMoveUp()
' Select cell A3 then run Macro
' it will be cleared and values below moved up
Dim lr As Long
Dim R As Range
lr = Cells(Rows.Count, "A").End(xlUp).Row
ActiveCell.ClearContents
If lr = ActiveCell.Row Then
ActiveCell.Delete Shift:=xlUp
Exit Sub
End If
Set R = Cells(lr, "A")
Range(ActiveCell.Offset(1, 0), R).Cut _
Destination:=ActiveCell
End Sub


HTH,
--
Data Hog


"ordnance1" wrote:

> Sorry you are of course correct. What I intended to say was cells A1, A2, A3,
> A4 and A5.
>
> A1 Pat
> A2 Ron
> A3 Dave
> A4 Linda
> A5 Sam
>
> If I remove the name from A3 then everything below A3 the names below should
> move up. I due to the structure of the worksheet I cannot just delete the cell
> A1 Pat
> A2 Ron
> A3 Linda
> A4 Sam
> A5
>
>
> "JLatham" wrote:
>
> > Please recheck the way you've described the problem. You say names are in
> > A1, B1, C1, D1 and E1. Then you say when C1 is deleted you want D1 and E1 to
> > "move up". There is no "up" from row 1. And since if only C1 were deleted,
> > D1 and E1 would still have names in them, so you couldn't move D2:D# and
> > E2:E# up without it overwriting the current contents of D1 and E1.
> >
> > Also, give us some before and after examples and we may be able to help you.
> >
> > "ordnance1" wrote:
> >
> > > I have cells A1, B1, C1, D1 and E1 each contains a name. What I need to do
> > > is, if the contents of say cell C1 is deleted the I need the contents of
> > > cells D1 and E1 to move up. It is critical that that the name remain in the
> > > same order (there by precluding a normal sort). So this would have to work
> > > regardless of which cell(s) are cleared.
 | 
Pages: 1
Prev: sheet2 = sheet1
Next: Variant Array Copy, Array row contains Object