Top to bottom

[TyeJae]

Ok here is my dilema. I have a list of names. At the press of a button I want to have the top person on the list of names to go to the bottom and the full list of names shift upwards so there is no empty space. For example...

a1= Susie
a2= John
a3= Bob
a4= Sarah

Now when I press the button I want it to read...

a1= John
a2= Bob
a3= Sarah
a4= Susie

Then of course if I pressed it again...

a1= Bob
a2= Sarah
a3= Susie
a4= John

And so on...

Could anyone help me out with this?

TyeJae

[TyeJae]

Here is what I have right now, but is there a way to do it without deleting that cell like I am doing?

Private Sub CommandButton1_Click()
Range("A13").Value = Range("A2")
Range("A2").Value = ""
Range("A2").Select
If Range("A2").Value = "" Then
Selection.Delete Shift:=xlUp
End If
End Sub

And also A13 is currently the end of the list, but I don't want it to necessarily be A13 always because if I add a name I would have to change the formula every time.

[TyeJae]

Anybody have a take on this?

[bigwheel]

Well, this assumes the cell under your last name is blank:-

firstadd = Range("a2").Offset(1, 0).Address
lastadd = Range("A65536").End(xlUp).Offset(2, 0).Address
Range("A65536").End(xlUp).Offset(1, 0) = Range("A2")
Range(firstadd, lastadd).Copy
Range("A2").PasteSpecial
Range("A1").Select

"TyeJae" wrote:

>
> Anybody have a take on this?
>
>
> --
> TyeJae
> ------------------------------------------------------------------------
> TyeJae's Profile: http://www.excelforum.com/member.php...fo&userid=7233
> View this thread: http://www.excelforum.com/showthread...hreadid=465701
>
>

[TyeJae]

That works but I loose all formatting in my cells...

[Rowan]

I'm not sure why you don't want to delete the cell, unless you have
other data on the row you want to keep in line, but otherwise how about:

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim eRow As Long
eRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(eRow, 1).Value = Cells(2, 1).Value
Cells(2, 1).Delete Shift:=xlUp
Application.ScreenUpdating = True
End Sub

Hope this helps
Rowan

TyeJae wrote:
> Here is what I have right now, but is there a way to do it without
> deleting that cell like I am doing?
>
> Private Sub CommandButton1_Click()
> Range("A13").Value = Range("A2")
> Range("A2").Value = ""
> Range("A2").Select
> If Range("A2").Value = "" Then
> Selection.Delete Shift:=xlUp
> End If
> End Sub
>
> And also A13 is currently the end of the list, but I don't want it to
> necessarily be A13 always because if I add a name I would have to
> change the formula every time.
>
>

[TyeJae]

The reason I don't want to delete the row is because I have formating which this last post I loose the formating too, but it works really well. Is there a way to do this where I don't loose the formatting?

TyeJae

[Rowan]

One way would be to use a blank column to store the formatting while you
do the delete (column E in my example):

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim eRow As Long
eRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Range(Cells(2, 1), Cells(eRow - 1, 1)).Copy
Cells(2, 5).PasteSpecial Paste:=xlPasteFormats
Cells(eRow, 1).Value = Cells(2, 1).Value
Cells(2, 1).Delete Shift:=xlUp
Range(Cells(2, 5), Cells(eRow - 1, 5)).Copy
Cells(2, 1).PasteSpecial Paste:=xlPasteFormats
Range(Cells(2, 5), Cells(eRow - 1, 5)).Clear
Application.ScreenUpdating = True
End Sub

Hope this helps
Rowan

TyeJae wrote:
> The reason I don't want to delete the row is because I have formating
> which this last post I loose the formating too, but it works really
> well. Is there a way to do this where I don't loose the formatting?
>
> TyeJae
>
>

[TyeJae]

This works great...thank you for your help Rowan!!