automatic sort #2

I have a spreadsheet with columns A:G and unlimited rows.
Is there any way to automatically sort the whole sheet in alphabetical
order by, let say column G?
The problem is that users of this sheet enter data in the rows and I
need it to be sorted as I mentioned above but many of them don't know
much Excel and can't do the sort from the menu so I was thinking maybe
some kind of macro could do it but I don't know how to write it.
I should mention that I already have macro on that sheet that sets up
printing area, also with ctrl+D it deletes the current row and moves
everything up so if I add another one....
I just don't know how to do it so I need some help, please

Mark
0
123 (29)
3/19/2005 1:55:04 AM
excel 39879 articles. 2 followers. Follow

4 Replies
598 Views

Similar Articles

[PageSpeed] 26

A macro just to sort on one column is probably more trouble than it's worth,
and there is no need for anyone to use the sort command in the menu to sort
on a single column. All they need do is click in column G (per your example)
& click either the A-Z (Ascending Sort) or Z-A (Descending Sort) button on
the toolbar.

HTH |:>)


On 3/18/05 8:55 PM, in article 421n31tff4ldu6v8h7kd8k9qd6nbc7ske2@4ax.com,
"Mark" wrote:

> I have a spreadsheet with columns A:G and unlimited rows.
> Is there any way to automatically sort the whole sheet in alphabetical
> order by, let say column G?
> The problem is that users of this sheet enter data in the rows and I
> need it to be sorted as I mentioned above but many of them don't know
> much Excel and can't do the sort from the menu so I was thinking maybe
> some kind of macro could do it but I don't know how to write it.
> I should mention that I already have macro on that sheet that sets up
> printing area, also with ctrl+D it deletes the current row and moves
> everything up so if I add another one....
> I just don't know how to do it so I need some help, please
> 
> Mark

-- generaltaz1@comSpaMcast.net


0
xyz410 (17)
3/19/2005 3:10:07 AM
I'd put a button from the forms toolbar on the worksheet and assign this macro
to it.

Option Explicit
Sub sortMyData()

    Dim LastRow As Long
    Dim myRng As Range
        
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set myRng = .Range("a1:G" & LastRow)
        myRng.Sort Key1:=.Columns(7), Order1:=xlAscending, _
            Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
            Orientation:=xlTopToBottom
    End With

End Sub

I assumed that you can find the lastrow of the range by looking at column A. 
Adjust that if necessary.

And I assumed that you had headers in Row 1 and details from row 2 down.

Mark wrote:
> 
> I have a spreadsheet with columns A:G and unlimited rows.
> Is there any way to automatically sort the whole sheet in alphabetical
> order by, let say column G?
> The problem is that users of this sheet enter data in the rows and I
> need it to be sorted as I mentioned above but many of them don't know
> much Excel and can't do the sort from the menu so I was thinking maybe
> some kind of macro could do it but I don't know how to write it.
> I should mention that I already have macro on that sheet that sets up
> printing area, also with ctrl+D it deletes the current row and moves
> everything up so if I add another one....
> I just don't know how to do it so I need some help, please
> 
> Mark

-- 

Dave Peterson
0
ec357201 (5290)
3/19/2005 11:32:26 AM
I'd be very careful with this.  Depending on your selection, excel can guess the
wrong range to sort.  

And if, by mistake, the users sort just column G, it'll be difficult to put the
data back the way it should be.


CyberTaz wrote:
> 
> A macro just to sort on one column is probably more trouble than it's worth,
> and there is no need for anyone to use the sort command in the menu to sort
> on a single column. All they need do is click in column G (per your example)
> & click either the A-Z (Ascending Sort) or Z-A (Descending Sort) button on
> the toolbar.
> 
> HTH |:>)
> 
> On 3/18/05 8:55 PM, in article 421n31tff4ldu6v8h7kd8k9qd6nbc7ske2@4ax.com,
> "Mark" wrote:
> 
> > I have a spreadsheet with columns A:G and unlimited rows.
> > Is there any way to automatically sort the whole sheet in alphabetical
> > order by, let say column G?
> > The problem is that users of this sheet enter data in the rows and I
> > need it to be sorted as I mentioned above but many of them don't know
> > much Excel and can't do the sort from the menu so I was thinking maybe
> > some kind of macro could do it but I don't know how to write it.
> > I should mention that I already have macro on that sheet that sets up
> > printing area, also with ctrl+D it deletes the current row and moves
> > everything up so if I add another one....
> > I just don't know how to do it so I need some help, please
> >
> > Mark
> 
> -- generaltaz1@comSpaMcast.net

-- 

Dave Peterson
0
ec357201 (5290)
3/19/2005 11:34:15 AM
On Sat, 19 Mar 2005 05:32:26 -0600, Dave Peterson
<ec35720@netscapeXSPAM.com> wrote:

>I'd put a button from the forms toolbar on the worksheet and assign this macro
>to it.
>
>Option Explicit
>Sub sortMyData()
>
>    Dim LastRow As Long
>    Dim myRng As Range
>        
>    With ActiveSheet
>        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
>        Set myRng = .Range("a1:G" & LastRow)
>        myRng.Sort Key1:=.Columns(7), Order1:=xlAscending, _
>            Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
>            Orientation:=xlTopToBottom
>    End With
>
>End Sub
>
>I assumed that you can find the lastrow of the range by looking at column A. 
>Adjust that if necessary.
>
>And I assumed that you had headers in Row 1 and details from row 2 down.
>
Thanks Dave
I'll try this

Mark
0
123 (29)
3/19/2005 7:44:16 PM
Reply:

Similar Artilces:

Pivot Table Cycling Through Page Fields Automatically
Hi. I am trying to cycle through a complete set of data in one of the parameters in the "Page" field. For example, there are 500 investments, and I want to compute the internal rate of return (IRR) for each investment based on a series of cashflows for each investment. The IRR is a function that is placed outside the pivot table. As each investment number is chosen, the underlying pivot table cashflow data changes, allow the IRR function to pick up these cashflows and compute the IRR. However, if there are 500 investments, this becomes very time consuming - especially if the...

Problems printing in Publisher 2003 with HP PSC 2410 #2
I am new to publisher 2003 and am having great difficulties printing anything properly using an HP PSC 2410 all in one. As an example when trying to print a half page side folded pre-designed greetings card all that prints is page 1 and part of page 4, minus any text. Pages 2 and 3 are missing altogether. The print preview however displays everything correctly. I have reinstalled Office 2003 and updated the printer drivers to the latest versions but the problem remains. Any help is much appreciated. ...

Upgrading from Money 2002 #2
I have the original money 2002 that came with the computer and have never upgraded. Which is the best version to go to? More importantly, when I purchase and download the upgrade, will it go over the current program and update all the information that I currently have? Thanks and Happy New Year -- mfletcher The latest version in the US is Money+ aka Money 2008. Other geographies have different versions. Money keeps all your data in the .mny file not the program. When you install the new version it usually remembers where your .mny file was, makes a copy of it (for safety) and then up...

Automatic transfer/download match up
From my paycheck, I have $250 withdrawn and placed into a separate bank account. When I download my transactions for this separate bank account, it downloads the $250 transaction and doesn't realize this is the same transaction as the transfer from my main bank account. My question: is there anyway I can force Money to always match this transaction? The way I do it now is to delete the transaction that I downloaded and keep the one that shows as an automatic transfer. Thanks for any ideas! Joey ...

pantone colors #2
Is there a way to make Publisher 2007 show all Pantone Colors? I want 021 U. Find doesn't do it. B I don't think Publisher has all the Pantone colors. There are close equivalents. Not the same of course... C=0 M=53 Y=100 K=0 R=252 G=115 B=35 -- Mary Sauer http://msauer.mvps.org/ "Bob Wright" <mmpdallas@sbcglobal.net> wrote in message news:%23N1wilmHJHA.1364@TK2MSFTNGP04.phx.gbl... > Is there a way to make Publisher 2007 show all Pantone Colors? I want 021 U. > Find doesn't do it. > B Guess you are right, not all there. Weird. Close equivalents ...

Formulas don't work in certain cells #2
nope, the cells are formatted as numbers. I simply cannot figure thi out. -Jorda -- kalik24 ----------------------------------------------------------------------- kalik247's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1594 View this thread: http://www.excelforum.com/showthread.php?threadid=27423 Being formatted as numbers doesn't automatically mean they are numbers. Copy an empty cell formatted to General. Select your range of numbers and Paste Special>Add>OK>Esc Gord Dibben Excel MVP On Mon, 1 Nov 2004 11:59:46 -0600, kalik247 <kalik2...

Print Macro #2
Trying to write a macro to print a spreadsheet where the number of rows of data varies. What's wrong with this code? Dim rng As Range Set rng = Range("A1").End(xlDown).End(xlToRight) With ActiveSheet.PageSetup .PrintArea = rng End With Thank you. Pam Hi Pam Leave your PrintArea empty Excel will print all cells with a value on the sheet -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Pam" <gasp0225@juno.com> wrote in message news:16e401c38521$a7d25420$3501280a@phx.gbl... > Trying to write a macro to ...

How do I get excel files to open automatically from directories?
When I try to open excel files from the directory or from desktop I only get a blank worksheet not the file. I have to then go through File Open to get the file I want. There must be a way to have them open automatically. On Mon, 2 Jan 2006 21:22:01 -0800, Damian <Damian@discussions.microsoft.com> wrote: >When I try to open excel files from the directory or from desktop I only get >a blank worksheet not the file. I have to then go through File Open to get >the file I want. > >There must be a way to have them open automatically. Go to Tools -> Options -> Gen...

Importing data into Outlook #2
Is there a process or program with which I can transfer my ACT! data into Outlook contacts? Thanks! ...

Unable to send a message, its been trying for 24hrs #2
I have been tring to send an email that contains a photo, for the past 24hrs. it wont send. all I am able to see is "cannot open this file, Outlook has already begun transmission. It wont even let me delete the message http://www.howto-outlook.com/faq/messagestuckinoutbox.htm=20 --=81 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. =20 After furious head scratching, tillybird asked: | I have been tring to send an email that contains a photo, for the | past ...

How do insert name automatically atop of each pg of doc?
Hello, How do I automatically insert name on each page of document like inserting page numbers? My professor told me not to type it in but to insert and I have not been able to do so. This is a new program for me, Word 2007. I have to submit my paper in MLA form and do not wish to lose points because for this. Thanks for your help. Click on the Insert tab of the Ribbon and then in the Header and Footer section, click on Header and make your choice from the options presented or click on the Edit Header button to get a blank header pane in which to insert the name in the...

sorting formulas
Hi, Ive made a spreadsheet which all the cells accept titles contain look up formula's. I want to be able to do sorts on the columns but it doesnt seem to work. I think its because excel recognises the content of the cells as a formula, but i want it just to see the result of the look up and sort that. Is there anyway to solve this problem? Thanks to all who help. Matt -- m4tt ------------------------------------------------------------------------ m4tt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25835 View this thread: http://www.excelforum.com/s...

Outlook 2007: When i Save a New Rule outlook Change it automatically...
Hi people! I have this problem with Outlook 2007. I have 15 e-mails addresses in my outlook, but i can=B4t create the rules for send all the received mails to each emails folder. Example: I want to create a simple rule that move all the e-mail that i get from "example1@example1.com" to the folder "Inbox/ example1@example1.com". When i create the rule, all works fine, i select the correct e-mail account name, the correct destination folder, etc... but when I hit the SAVE button in rules window, Outlook 2007 changes the account automatically to another one. Not automatically...

Microsoft CRM 1.2 database export failed 01-27-06
I am receiving this message when I try to upgrade my CRM 1.2 to CRM 3.0, does any one have any idea what this means? ...

Ad Hoc Reporting #2
Hi, I am putting together an ad hoc report in Excel. My query is in the VB editor. I am trying to specify criteria Select x,y,z where... in the WHERE, I am struggling. I want to take a range of ids specified in the worksheet, F2:F55. So Select x, y, z where IDs IN ( F2:F55 ) I tried to use WHERE IN ( Range(F2:F55) ) but it did not like it. What syntax should I use to make this work? Thanks! ...

Reinstall #2
I am reinstalling Office 2003 on a new computer. I have installed the software but I the contact information and calendars, messages, notes etc have not transferred to the new one. How can I do that? ...

GetTextExtentPoint32 #2
Hi, I have multiple problems here :( I have a short text in UNICODE and the text contains newlines as well (carriage return and newline) and it seems that GetTextExtentPoint32 doesn't take into account the newlines. So I broke the lines myself and checked which has the longest length. Also, for the "int cbString" parameter, if my text is "hello" and I pass 5, then it doesn't return the current width. I have to send 6. Yes, I do select my font in the HDC before calling this. Other problem! Interline spacing issue Lines are separated by a certain mount of pixels...

Incoming Emails #2
Do I always have to click the "Send/Receive" button in order to get my new emails? No, you can go to TOOLS, then SEND/RECEIVE SETTINGS and then DEFINE SEND/RECEIVE SETTINGS and in the box's shown, you can tell Outlook to automatically send or receive your messages every XX mins. Tedd -- Tedd Riggs PDASquare Content Developer www.pdasquire.com "wj" <anonymous@discussions.microsoft.com> wrote in message news:88174331-72B4-4ECF-B688-DF745D9178EF@microsoft.com... > Do I always have to click the "Send/Receive" button in order to get my new emails? T...

Automatic update of information in a spreadsheet
Hi, I am using Microsoft Excel 2000. I have just done a spreadsheet which contains information about patients. In one worksheet I have the raw data and then in others I have copied columns across and then used IF(AND) statements to abstract the information I need, e.g. to work out how many males there are in each of the hospitals. What I need to know is how do I set it up so that when a new entry is added to the raw data, i.e. another patient in a new row, it automatically updates the information in the other worksheets? What is happening at the moment is when I add another row the IF s...

outlook client problem #2
Hi I have a one user that have problem with outlook client are always prompt to login outlook 2007 crmclient V3C and have all patch install You might want to create a new profile for that client. Also check that they are running SQL 2005 Express on their desktop as opposed to an MSDE instance. Their was a shift in databases from 2000 to 2005 during the life of CRM v3.0 "sjm" wrote: > Hi > I have a one user that have problem with outlook client are always prompt to > login > outlook 2007 > crmclient V3C and have all patch install Hi sjm, Is the user also prom...

CTreeView #2
I use TVS_SHOWSELALWAYS style with the tree control. When the focus from the view is lost, the selection bar turns pale grey. Is there a way I could change the color of selection bar? Thanks in advance You may be able to use OnCustomDraw() to do this sort of thing. http://www.pocketpcdn.com/articles/tree_color.html http://cvs.sourceforge.net/viewcvs.py/shareaza/shareaza/CtrlSharedFolder.cpp?rev=1.3 http://www.codeguru.com/Cpp/controls/treeview/newlistviewcontrolie40/article.php/c711/ Tom "RG" <nobody@nowhere.com> wrote in message news:eZSucQKrFHA.3264@TK2MSFTNGP12...

Attachment security in Outlook 2003 #2
I had the same problem. I downloaded DetachOL form the link below and it fixed all my problems. You can choose different levels of security. Hope this helps http://www.mcdev.com/outlook.shtml ...

How to prevent MSN Messenger from Automatically Starting Up With Outlook 2003
I've searched this problem on google groups and have people post wrong information about this. HERE IS THE CORRECT WAY TO DO IT: In Outlook go to Tools>Options>Other. Uncheck the box that says "Enable the person names smart tag". And you're done! Just a follow-up: To be more accurate, you need to uncheck "Display messenger status in the From field". [Unchecking "Enable the person names smart tag" will disable them both, but it is the second one that is actually responsible for starting up MSN messenger]. ktedja@excite.com (Rich Gvir) wrote in m...

X Axis needed for Line Chart #2
I have created two line images on a Line Chart using several data points. Now I would like to have an x axis that shows a marker for every 1/2 hour. Example: .5 1.0 1.5 2.0 2.5 3.0 and so on for up to 5.0 to represent hours. I created the data for these time points that coorespond to my data for line images but do not know how to show them in my x axis as markers. Any idea? My x axis shows nothing? Y axis is fine. I have data scale markers there. -- FL Does the chart know you have X data? Go to Chart menu > Source Data > Series tab. Is there anything in the ...

Is there a way to automatically delete duplicate entries in Excel.
Is there a way to automatically delete duplicate entries in Excel? If there are Invoice #'s or some other numerical value, can they be deleted or selected without having to search through the document or filter? Hi use 'Data - filter - Advanced Filter' and check 'Unique entries' -- Regards Frank Kabel Frankfurt, Germany NK wrote: > Is there a way to automatically delete duplicate entries in Excel? > If there are Invoice #'s or some other numerical value, can they be > deleted or selected without having to search through the document or > filter? N...