Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Top to bottom
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 ------------------------------------------------------------------------ TyeJae's Profile: http://www.excelforum.com/member.php...fo&userid=7233 View this thread: http://www.excelforum.com/showthread...hreadid=465701 |
#2
|
|||
|
|||
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 ------------------------------------------------------------------------ TyeJae's Profile: http://www.excelforum.com/member.php...fo&userid=7233 View this thread: http://www.excelforum.com/showthread...hreadid=465701 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
That works but I loose all formatting in my cells... -- TyeJae ------------------------------------------------------------------------ TyeJae's Profile: http://www.excelforum.com/member.php...fo&userid=7233 View this thread: http://www.excelforum.com/showthread...hreadid=465701 |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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 ------------------------------------------------------------------------ TyeJae's Profile: http://www.excelforum.com/member.php...fo&userid=7233 View this thread: http://www.excelforum.com/showthread...hreadid=465701 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
This works great...thank you for your help Rowan!! -- TyeJae ------------------------------------------------------------------------ TyeJae's Profile: http://www.excelforum.com/member.php...fo&userid=7233 View this thread: http://www.excelforum.com/showthread...hreadid=465701 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rows to repeat at BOTTOM. | Setting up and Configuration of Excel | |||
Finding Bottom of Sheet | Excel Worksheet Functions | |||
Tabs accross of bottom of sheet are not showing | Excel Discussion (Misc queries) | |||
how to paste data from top to bottom to bottom to top | Excel Discussion (Misc queries) | |||
how to print a row on the bottom of every page | Excel Discussion (Misc queries) |