Sort issue Sort Of

[ordnance1]

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.

 

[JLatham]

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# 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]

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

 

[J_Knowles]

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,