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?action=getinfo&userid=7233
View this thread: http://www.excelforum.com/showthread.php?threadid=465701

0
9/7/2005 8:15:08 PM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
391 Views

Similar Articles

[PageSpeed] 35

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?action=getinfo&userid=7233
View this thread: http://www.excelforum.com/showthread.php?threadid=465701

0
9/7/2005 8:21:17 PM
Anybody have a take on this?


-- 
TyeJae
------------------------------------------------------------------------
TyeJae's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7233
View this thread: http://www.excelforum.com/showthread.php?threadid=465701

0
9/7/2005 9:59:29 PM
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?action=getinfo&userid=7233
> View this thread: http://www.excelforum.com/showthread.php?threadid=465701
> 
> 
0
bigwheel926 (227)
9/7/2005 10:37:01 PM
That works but I loose all formatting in my cells...


-- 
TyeJae
------------------------------------------------------------------------
TyeJae's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7233
View this thread: http://www.excelforum.com/showthread.php?threadid=465701

0
9/7/2005 11:35:43 PM
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.
> 
> 
0
rowanzsa (27)
9/8/2005 12:46:13 AM
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?action=getinfo&userid=7233
View this thread: http://www.excelforum.com/showthread.php?threadid=465701

0
9/8/2005 1:23:09 AM
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
> 
> 
0
rowanzsa (27)
9/8/2005 3:41:01 AM
This works great...thank you for your help Rowan!

--
TyeJa
-----------------------------------------------------------------------
TyeJae's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=723
View this thread: http://www.excelforum.com/showthread.php?threadid=46570

0
9/8/2005 9:32:06 PM
Reply:

Similar Artilces:

Actor: Top package
Hey... When i create an actor it display the text "top package" before the name i give it... How do i remove that??? Best regards Kent ...

TOP 15 online business
This is the best place to find Top Online and Home Business The best of the best is there. Subscribe to get lattest news Updated daily TOP 15 online business: http://www.spot2biz.com ...

PivotTable
Re: http://www.contextures.com/xlPivot05.html#TopItems I use the data file from the above site. I drag "name" to the row fields area and "score" to the data items area. = Then I double-click on name and select top 5 scores. I can even sort = in ascending order of scores. No problem. However, if I just drag score to the row fields area and nothing else, = when I click on advanced, "Top 10 AutoShow" is greyed out. The way to = get around this is to also drag score to the data area i.e. two = identical columns of score side by side. Then I can choose the top...

Forcing a dialog to be the top top top top most window !
Hi When my app starts up it displays an initial dialog. Under certain circumstances this dialog popus up behind another applications window so you don't see it. Is there any way of forcing the dialog to the top I have set the dialog's "Set Foreground" property I have added the following to its OnInitDialog() : SetWindowPos(&wndTop, 0, 0, 0, 0, SWP_NOMOVE | SWP_NOSIZE ) TTFN Jo &wndTopmost joe On Fri, 21 May 2004 02:51:03 -0700, Jon EVans <anonymous@discussions.microsoft.com> wrote: >Hi, > >When my app starts up it displays an initial d...

How to make CStatic been on top of all child windows in my dialog?
Hi there. I'm just expiriencing some problems with CStatic behaviour. The point is that I have some CStatic that I want to use as a nitification label. sometimes I need to show user some information and I want to do that the way when a Label appeare in the middle of the dialog and stay on top of all other controls placed o dialog. I use the following functions to make a CStatic visible and been on top, but it doesn't work well: ::SetWindowPos(m_lblPartsMsg.m_hWnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE | WP_NOSIZE ); Coudl you suggest me what I have to do to acchive the behaviour I ...

How can i make values appear at the top of bars in a bar graph?
chart options>data labels>value -- Don Guillett SalesAid Software donaldb@281.com "Dylan" <Dylan@discussions.microsoft.com> wrote in message news:9AF2AB4B-03DE-4A30-9435-83C9C517D92F@microsoft.com... > ...

List top five
Hi there, I've searched this site looking for an answer for this but no luck What I have is imported list of invoices thousands of rows long with about 450 suppliers occurring many times over. What I want is a formula or function that will give me the top 5 suppliers - something like (MAX(Count,1 ,2, ,3...?? It seems it should be pretty straightforward but it's beaten me (I can do this easily with a filter but I've been told they find them confusing!) Many thanks for your help Paul SInce you have your data in rows already, you could create a PivotTable w...

64bit on top
http://store.steampowered.com/hwsurvey Steam's hardware survey has Win7 x64 as most popular OS barely beating XP. ...

**Top Best GreenPennyStock: MDOR** Revolutionizing Rubber
***MDOR***(trading heavy volume at $0.77 PPS but analyst are calling for $2.51 minimum) MDOR just Revolutionized the way Rubber is Recycled and Reactivated, this will immediately give MDOR a Monopoly on the WorlWide Markets. MDOR just recently broke a NEW 52 week high on the HIGHEST volume ever!!!! **Magnum/SRI Releases Phase II Results of Custom Compounds Test** BREAKING NEWS JUST OUT http://magnumresources.net/view-investors.php?id=3D1= 80 Time to buy is now on the small healthy pullback, hurry before MDOR explodes again and makes more people RICH!!!!! Breaking News just out on MDOR, I...

Printing the top of a table on every sheet.
Ok, I must be missing something here but I just can't figure it out. I effect, I have a big table with the first 3 rows being all semi comple column headings (i.e. underlined cells, centered, justified....lots o formatting). I want to get the top of this table to print at the to of each sheet of the table. I look at the header formatting dialo boxes and I don't see anything that will help. Cliff Notes: How do you make top rows of table print on every sheet? p.s. sorry if this is the 15,000th time this has been asked, but searched 300posts without finding it -- Message posted f...

Getting text to read bottom to top
Good afternoon. I have text in a text box that I'd like to read from bottom-to-top rather than left-to-right. I can rotate the text box, but the text itself isn't rotating. Click the Text Direction button (in Word 2003 or earlier it's on the Text Box toolbar; in Word 2007/2010 it's on the Text Box Tools ribbon). The first click changes the direction to top-to-bottom and the second click changes it to bottom-to-top. A third click will return it to left-to-right. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be ack...

in excel where it says a b c d on top how to shrink that
in excel in the colum where it says a,b ,c, how to shrink that row. Do you mean by hide column headers? The procedure how to hide row and column headers Tools > Options > View > uncheck Row & column headers > OK out "khalid" wrote: > in excel in the colum where it says a,b ,c, how to shrink that row. ...

I can not move or scroll to the top rows (7 to 1) #2
Thank you Dave. Dave Peterson Wrote: > Window|unfreeze panes. > > This is used to keep those top rows always visible. > > sax30 wrote:- > > Hi Everyone, > > I am not sure what has happened, but I just can not scroll up to the > top rows in my excel worksheet. I can scroll up to row 8 and i > stopped > there. I see a black line across and not sure of how to fix it. An > help > is greatly appreciated. Thanks. > > -- > sax30- > > -- > > Dave Peterso -- sax30 You're welcome. sax30 wrote: > > Thank you Dave. > D...

Rename the top organization
Hi, When installing CRM 3, I made a spell mistake in the name of the top organization : is it possible to rename the organization after the install ? Thanks, Fabien Majurel Whilst you can modify the database directly (OragnizationBase table) and rename the databases, is not supported nor recommended. You are far better off just reinstalling, which is the supported way of doing this. HTH, R. "Fabien Majurel" wrote: > Hi, > > When installing CRM 3, I made a spell mistake in the name of the top > organization : is it possible to rename the organization after th...

Highlighting top row to last row in sheet
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Is there a short cut that allows me to select the top row off my sheet and then jump to the last row so I can highlight the entire sheet, scrolling through 15000 lines is slow and tedious. <br><br>Thanks Command + a On 31/05/10 4:12 PM, in article 59bb92f0.-1@webcrossing.JaKIaxP2ac0, "taup41@officeformac.com" <taup41@officeformac.com> wrote: > Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Is > there a short cut that allows me to select the top r...

Change left bar and top bar names on Order screen
Ok, I guess this could be the left bar and top bar names for any screen, but I'm specifically asking about the Order screen. I have a client that wants to change "Create Invoice" at the top to "Close". They also want to change "Write-In Products" to "Write-In Rooms". Where do I change these? I don't see them as options in the customization screens, even in the N:1 and 1:N areas. On Sep 23, 2:18=A0pm, Wayne Walton <waynek...@gmail.com> wrote: > Ok, I guess this could be the left bar and top bar names for any > screen, but I'm ...

How to keep the top of the form anchored at the top
Hi there I have created a form and subform where the subform is a tab control. When I open my form, I'm at the top of the page but when I start to select any of the tabs, I'm no longer at the top of the form but am located near the bottom. How do I set it so that my form always stay at the top instead of moving around? Thank you in advance for your help My guess would be that the taborder setting is not as expected. Make sure the taborder settings for the various fields are set in a logical way. Also make sure that the subform height is a little smaller than ...

Data Validation List box scroll to the top of list
I have a few cells with a Data Validation option that will allow values only from a given list. In this list I have about 10 lines with actual values and about 20 additional empty cells. The problem I am facing is when I click on the cell with the Data validation, Excel shows me a blank list which is basically the middle of the exhaustive list with the actual values only in the top of the list. How can I get Excel to always display the list beginning with the top of the list? ...

Text boxes make cursor skip bottom of page?
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hello, <br><br>I'm a teacher and use a lot of text boxes in making worksheets. A lot of times, though, using text boxes messes up something with the page formatting. The margins don't change but when I get to about 2-3 inches above the margin, it skips to a new page. (This distance might actually be the height of my text box.) The only solution I have found is to copy and paste everything into a new document and put the text boxes in at the end, but I'd like to not have to do that. <br>...

display mail item in window on top
I have suddenly started having the following problem when I try to view individual mail items... When I double-click on a mail item to view it, the item opens in a new window UNDERNEATH Outlook. To access it, I have to either double-click a second time (which brings the window to the front) or click on the portion of the mail window that is showing. I cannot find a way to change any of the settings so that the mail item will display on TOP. I have even gone as far as to completely reinstall Outlook. That actually did fix the problem -- for about 3 hours. Any suggestions? See anot...

I get delete yes or no at the bottom of page when I hit the delet.
How can I delete previous senders of an e-mail message when I attempt to foreward it. I now have to hit a Y or N to remove and part of a message. "Fritz" <Fritz@discussions.microsoft.com> wrote in message news:B857A822-0775-435F-852A-FF5389CBADE2@microsoft.com... > How can I delete previous senders of an e-mail message when I attempt to > foreward it. I now have to hit a Y or N to remove and part of a message. Just highlight and delete. That's what I always do.... Version of Outlook? Are you using word as the editor? (I seem to recall this comes ...

Selected application/file not on top
If I currently have 18 windows open (and yes, I work with that many open at times) of various programs, and I open another file from a shortcut or from the Desk Top's "My Documents", or from Recent Documents, it opens minimized with all the other windows at the bottom of the screen, then I have to look for it and click on it to maximize it. Why does it open in a minimized status? Is there a way to change this? Connie In news:D0C235BB-0E9E-437E-9B88-520CDE7968A1@microsoft.com, Connie Martin <ConnieMartin@discussions.microsoft.com> typed: > If I currently...

Top value
Hi, i have a table like this: john 4500 john 1200 mary 900 jim 5000 john 3700 mary 2300 jim 1400 And i need formulas to extract top 5 values: I need something like this: John 9400 jim 6400 .....etc Can this be done? Thanks! Assign header to both columns and use Pivot Table. Data>Pivot table....Drag and drop name to 'Row field' and the values to Data items area.. set AutoSort options from Right click>Field Settings>Advanced> -- Jacob (MVP - Excel) "puiuluipui"...

sorting; blanks at the top; what is a "blank"
I have multiple columns which are populated with function return values. The cells are text format. If the function (vlookup) doesn't find it's target, I enter a "blank" (two double quotes) - or I think I do. But these blanks do not always sort last. If I copy and paste into notepad I get a newline. Is there a function that returns "blank" I can use to populate a cell? I've also tried the general format. This is a very simple sort: select X rows Data->Sort [Sort Col A, Ascending, No header row] Thank you Those empty strings will sort t...

What does the green tag in the top left of a cell mean?
I am preparing a table with values and percentages but in some areas (where I'm doing a % calculation) a small green tag appears in the top left of the cell with an exclamation mark. It suggests an error but the calculation is right. What does the green tag mean and how do I get rid of it? xl2002 added some checks--things like numbers stored as text or inconsistent formulas. You can see all those things that excel will check under: tools|Options|Error checking If you click on that little exclamation point, you'll see what excel thinks may be wrong. You can tell excel to to ign...