sorting macro #3

I need to sort on 8 cells in a row and on 1500+rows. I can do it one at a 
time but I know there must be an easier way. Can ANYONE HELP? 

C3:J3 I need these cells sorted and then down to C1532:J1532

I did create a macro to sort after I selected them but still one at a time. 
Does it have to be a macro? I can edit a macro but don't really know how to 
program them.
0
GLeeds (3)
8/10/2009 8:26:01 PM
excel 39879 articles. 2 followers. Follow

4 Replies
1514 Views

Similar Articles

[PageSpeed] 0

Yes, it has to be a macro.  The following should do it.  This macro loops 
through all the entries in Column C, and in each row it sorts the values in 
Columns C:J.  HTH  Otto
Sub SortRows()
    Dim rColC As Range
    Dim i As Range
    Dim TheRow As Range
    Dim RngToSort As Range
    Set rColC = Range("C3", Range("C" & Rows.Count).End(xlUp))
    Set TheRow = Range("C1:J1")
    For Each i In rColC
        Set RngToSort = TheRow.Offset(i.Row - 1)
        RngToSort.Sort Key1:=Cells(i.Row, 3), Order1:=xlAscending, 
Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
    Next i
End Sub
"GLeeds" <GLeeds@discussions.microsoft.com> wrote in message 
news:82AC5466-4463-47EB-A2BC-9841EB8B2736@microsoft.com...
>I need to sort on 8 cells in a row and on 1500+rows. I can do it one at a
> time but I know there must be an easier way. Can ANYONE HELP?
>
> C3:J3 I need these cells sorted and then down to C1532:J1532
>
> I did create a macro to sort after I selected them but still one at a 
> time.
> Does it have to be a macro? I can edit a macro but don't really know how 
> to
> program them. 


0
8/10/2009 9:30:42 PM
Sub sortcolumnsbyrow()
For i = 3 To Cells(Rows.Count, 3).End(xlUp).Row
Cells(i, 3).Resize(, 8).Sort Key1:=Cells(i, 3), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlLeftToRight
Next i
End Sub


-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"GLeeds" <GLeeds@discussions.microsoft.com> wrote in message 
news:82AC5466-4463-47EB-A2BC-9841EB8B2736@microsoft.com...
>I need to sort on 8 cells in a row and on 1500+rows. I can do it one at a
> time but I know there must be an easier way. Can ANYONE HELP?
>
> C3:J3 I need these cells sorted and then down to C1532:J1532
>
> I did create a macro to sort after I selected them but still one at a 
> time.
> Does it have to be a macro? I can edit a macro but don't really know how 
> to
> program them. 

0
dguillett1 (2487)
8/10/2009 9:50:04 PM
Hi Otto,

Thank you for your reponse. I tried Don's first and it worked great.

Thanks again

"Otto Moehrbach" wrote:

> Yes, it has to be a macro.  The following should do it.  This macro loops 
> through all the entries in Column C, and in each row it sorts the values in 
> Columns C:J.  HTH  Otto
> Sub SortRows()
>     Dim rColC As Range
>     Dim i As Range
>     Dim TheRow As Range
>     Dim RngToSort As Range
>     Set rColC = Range("C3", Range("C" & Rows.Count).End(xlUp))
>     Set TheRow = Range("C1:J1")
>     For Each i In rColC
>         Set RngToSort = TheRow.Offset(i.Row - 1)
>         RngToSort.Sort Key1:=Cells(i.Row, 3), Order1:=xlAscending, 
> Header:=xlNo, _
>         OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
>     Next i
> End Sub
> "GLeeds" <GLeeds@discussions.microsoft.com> wrote in message 
> news:82AC5466-4463-47EB-A2BC-9841EB8B2736@microsoft.com...
> >I need to sort on 8 cells in a row and on 1500+rows. I can do it one at a
> > time but I know there must be an easier way. Can ANYONE HELP?
> >
> > C3:J3 I need these cells sorted and then down to C1532:J1532
> >
> > I did create a macro to sort after I selected them but still one at a 
> > time.
> > Does it have to be a macro? I can edit a macro but don't really know how 
> > to
> > program them. 
> 
> 
> 
0
GLeeds (3)
8/11/2009 1:08:21 AM
Hi Don,


Thank you sooo much.. This works great. I was getting missed calcs in the 
beginning but found I gave you some wrong info. The number of cells was 7 not 
8 so one minor change and WOW. Works like a charm. It's great to have a 
format like this for someone like me to get some fast easy answers..  Thank 
you MS....

Thanks again....

"Don Guillett" wrote:

> Sub sortcolumnsbyrow()
> For i = 3 To Cells(Rows.Count, 3).End(xlUp).Row
> Cells(i, 3).Resize(, 8).Sort Key1:=Cells(i, 3), _
> Order1:=xlAscending, Header:=xlGuess, _
> OrderCustom:=1, MatchCase:=False, _
> Orientation:=xlLeftToRight
> Next i
> End Sub
> 
> 
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett1@austin.rr.com
> "GLeeds" <GLeeds@discussions.microsoft.com> wrote in message 
> news:82AC5466-4463-47EB-A2BC-9841EB8B2736@microsoft.com...
> >I need to sort on 8 cells in a row and on 1500+rows. I can do it one at a
> > time but I know there must be an easier way. Can ANYONE HELP?
> >
> > C3:J3 I need these cells sorted and then down to C1532:J1532
> >
> > I did create a macro to sort after I selected them but still one at a 
> > time.
> > Does it have to be a macro? I can edit a macro but don't really know how 
> > to
> > program them. 
> 
> 
0
GLeeds (3)
8/11/2009 1:14:01 AM
Reply:

Similar Artilces:

Sort not working correctly
I created a report for a query. When I sort the desired field most of the records sort correctly, but some records do not. This phenomenon happened both in the query and the report. I was able to solve the query sort problem by highlighting the column in the data set view and right click ascending. The mixed sort continues in the report weather I use Order By On or the Sorting and Grouping dialog box. Any idea what I'm doing wrong? -- Regards, Frank Frank <buckfd@gmail.com.(donotspam)> wrote: >I created a report for a query. When I sort the desired field most of the ...

close gp automatically after running a macro
Hi, Can anyone suggest the best way to close gp after running a macro. We are using macros to open GP to run an integration and need to close it afterwards. We are currently getting dexterity error messages. Thanks -- Jaime You can exit Dynamics during recording your macro so it will automatically exit during playback. Then add the following line as the second line of your macro. Logging file 'macro.log' This will prevent message from appearing within the macro and will log them to this file. -- Lorren Consulting Technology Lead WennSoft Inc. The views or opinions expres...

hyperlink to macro
Click on a hyperlink to execute a VBA macro. How to setup the Macro address and/or subadress fields for this? I remember seeing something for this a while back. You could use the Worksheet_FollowHyperlink() event as below. This runs the Sun RunCode in a standard module. Obviously you can test the values provided by the Hyperlink object (Target) if you only want to react to certain hyperlinks, etc. Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) RunCode End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS <alo...

sorting activities for each lead/contact by actual end dates
When I view the activities associated with each lead and contacts, the actual end date are not shown. Does anyone know how I can dispaly this field? The activities list in workplace does show the actual end dates but we'd like to see that when we access the list through indivial contact/lead record. Thanks, Victoria Hi Victoria, There is no good out of the box way to do this in MSCRM today. We're working hard to fix this for v2.0. Many of our customers have worked around this issue by registering for a post call out on the activity creation, and then having a custom object wh...

WSS 3.0 Search only works for administrator
Hello, I just moved WSS 3.0 contents to new SQL 2008 server. I did this by backing up the contents, wiping out the WSS on the server, re- installing it, setting up the new install on the new SQL server and restoring the content. Now search is working only for administrator. All other users that do a search doesn't return any results but doesn't get error either. It just says "No results matching your search were found." I am not sure what other information to provide for this problem. Please let me know if you need more information on this. Thanks, Did ...

Sorting in Excel 2003
When I sort columns that contain blank cells, Excel throws them down to the bottom. Is there any way to get them to appear at the top of the sort? Hi, You could try replacing the blanks with a space by clicking Crtl + H then leave go straight to replace with. Click in cell and press the space bar once. Then sort Ascending order VBA Noob -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33833 View this thread: http://www.excelforum.com/showthread.php?threadid=561267...

sort dates
hI My dates use the system dd/mm/year if i press the sort button.. it will sort by day->month->year I want to sort by year->month->day how do i do this?? thank -- Message posted from http://www.ExcelForum.com The best thing to do is to store your data as real Excel dates. Then they will always sort properly, regardless of which format you display the date in. As you have numbers, rather than dates, sorting isn't so easy. The choices are: 1. Create a Date field (=Date(year,month,day)) and sort on that. 2. Split out the day, month and year, then sort on the three separa...

How to change sort-by and sort order?
Hi, I am trying to figure out how to change the sort sort-by to "Product ID" instead of "Name" for the Product Record Lookup in Opportunities, Quotes and Orders. When you click the magnifying glass to "select a value for product", a web page dialog pops up. The "available records" view is soted alphabetically by the "Name". I would like to have the view alphabetically sorted by the "Product ID", and have the order of those columns swapped...i.e. Product ID column on the left, and Name column on the right. All advice greatly a...

Sorting out Duplicates
I have a price list with part numbers sorted on the left and price so the right... The genius who wrote the price list doubled up on a whol bunch of numbers. Is there a way to identify the duplicates ? 110204 110205 110206 110208 110208 110208 A-12322 B-12312 B-12312 B-12350 B-12367 Actually I know there is ... I have been using the following function after sorting by part number... A2 part number B2 =if(a2=a1,true,false) c2 =if(a2=a3,true,false) d2 =if(or(b2=true,c2=true),true,false) What that does is say true in the c column for dup part numbers.... What I am actually trying to d...

Max length of description on Appointement en CRM 3 ?
We adjusted the Max Length for the Description field in the Appointment record from the default of 2,000 to 5,000. When we use the web client we are able to save appointments that have 5,000 characters. However, when i go offline with my Laptop Client and then try the same thing the appointment form closes but there is a hidden error message behind it stating "The length exceeds the max length of it will be truncated". When you open the record back up instead of having 5,000 chars, you only have 2,000, i lost most of my text ? How setup Outlook to respect the 5,000 limit (as desi...

Create a Chart using a Macro
Hi all, I would like automatically generate a Gantt style graph though use of a Macro and a little button. I have the following data Task description Start date End date % completed % remaining. Does anyone have some code that I can just use? EXCEL 2007 Scott, I don't have a Macro but there is a GANTT chart available at:- http://www.pierrefondes.com/ Item Number 78. You might like to download this. If my comments have helped please hit Yes. Thanks. "Scott_goddard" wrote: > > Hi all, > > I would like automatical...

GP Macro Question
Does anyone know of a way to run a GP macro from the command prompt? it can be done for the login macro but i am trying to run it for a macro that i would like to occur while GP is already running and i cannot schedule it because it needs to be run at different times. any ideas? thanks for your help! -Josh ...

HELP! How do I get a total for columns sorted by account number on a spread sheet
Ok, I am not sure how else to explain this. On a spread sheet I will be entering account numbers and a balance due on different spots in the sheet. On a 2nd spread sheet I need it to find the specific account number and add up the total that customer owes. Can I do a total by account number by formula or will I have to do seperate spreadsheets for each person? If you can't tell I am lost. Laurie, Try something like =SUMIF(Sheet1!A1:A100,"12345678",Sheet1!B1:B100) where Sheet1!A1:A100 is the account number range, "123456768" is the account number to check, Sheet1...

Macro for Graph #2
For the following table, I have written the macro to obtain the graph But the problem is the no. of column and rows can change... So I don want to hard code particularly the first part of macro... Please help. Keyword Date Total Of Value XYZ TUV RST ABC 27/10/2003 7 7 11 100 ABC 27/11/2003 7 3 5 15 ABC 27/12/2003 7 10 200 25 Sub Graph() Charts.Add ActiveChart.ApplyCustomType ChartType:=xlBuiltIn TypeName:="Logarithmic" ActiveChart.SetSourceDat Source:=Sheets("Keyword_Analysis").Range("B2:F4"), PlotBy:=xlColumns ActiveChart.SeriesCollection(1).Delete ActiveC...

sorting a list
I have a column of faculty and need to sort it by rank in a particula order. There are four ranks. How is this done? thanks for your hel in advance -- dee studen ----------------------------------------------------------------------- dee student's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3480 View this thread: http://www.excelforum.com/showthread.php?threadid=55160 You could set up a custom sort list under Tools>Options>Custom Lists... import or type your four derivatives in the order you want to sort them. Once you have this set up, go to data...

MSXML 3.0 patch 955069 breaks our application
If I apply MS08-069 KB 955069 our application will not fully function.This Server has MSXML 4.0 on it as well. How to I get our application to point to another version of MSXML. I am not a developer. However I contated some of ours and they were of no help. Any help would be greatly appreciated. Dent wrote: > If I apply MS08-069 KB 955069 our application will not fully function.This > Server has MSXML 4.0 on it as well. How to I get our application to point to > another version of MSXML. What kind of application is that? For instance in classic ASP with VBScript you would ...

grouping/sorting limits
I have a need to have the maximum number of groups possible on a report (10), but I want each grouping to be sorted by a field that is different than the grouping field. Is there any way to add a sorting field to each of the 10 groups without deleting any groups? Not surprisingly, I was unsuccessful in trying to apply the sorting in the query. Thanks! Post sample data and what you want in results. -- KARL DEWEY Build a little - Test a little "AccessMan" wrote: > I have a need to have the maximum number of groups possible on a report (10), > but I want each grouping...

Instant messaging #3
Sorry I'm a newbi to ek2003, what componets should I have loaded to allow for instant messaging? I don't even have a virtual server for it? Thanks Pete. -- --------------------------------------------------------------------- "Are you still wasting your time with spam?... There is a solution!" Protected by GIANT Company's Spam Inspector The most powerful anti-spam software available. http://mail.spaminspector.com I believe IM capabilities are only available through Communications Server. If you are upgrading from Exchange 2000 and IM is already installed, you ca...

Sort command does not sort some columns?
When attempting to sort data, newly entered data seems to stay the same while the old data sorts itself. I have one whole column that is now mismatched. I am a newbie to this program so I am sure I am doing not doing something basic. BillBob: If you want to sort a multi-column range of cells: Select the entire range of cells <data><sort> Set your sort parameters Click the [OK] button. If all of the relevant columns are not pre-selected, they will not sort with the column you want sorted and you'll have out-of-sync mess. Does that help? *********** Regards, Ron XL...

Sort a Subtotalled List?
Hello, Does anyone know if you can sort a list after it has been subtotalled? The reason I ask is because I am basically sorting a list based on a criteria and then deleted all transactions that offset to each other to equal zero. So if I could sort the list after it was subtotalled all the subtotalled amounts that equaled zero would be in order and I could delete them all at once, instead of scanning through the list. Any help would save me a huge amount of time. Thanks, Would it be better to remove the subtotals (Data/Subtotals/Remove All) then sort/delete data, then re-apply t...

Adding #3
Hi, Does anyone have any suggestions for the following: I would like to add the last cell in column B (The row varies daily) o sheet 1 to the existing amount on Sheet 2 Cell A2. For example the last cell on Sheet 1 column B is $100. The amount o Sheet 2 Cell A2 is $5. I would like a Macro that would find the las cell on Sheet 1 Column B and add it to the existing value of Cell A Sheet 2 to create a new cell value of $105. Any help would be greatly appreciated -- STEVE ----------------------------------------------------------------------- STEVEB's Profile: http://www.excelforum.com...

matching a sorting the exact value in the data list
Hi there, In Excel 2000 I wish to match and sort the following data using some type of function in Excel. CRXU1972999 CRXU1972509 CRXU1973932 CRXU1972999 DFSU6001327 CRXU1973932 GESU2271222 FCIU2056631 GESU9254295 GESU2271222 PCIU3025511 PCIU2365754 PCIU3030457 PCIU3023571 PCIU3038329 PCIU3025511 PCIU3039304 PCIU3026020 PCIU3055060 PCIU3030457 PCIU3060600 PCIU3038329 PCIU3061993 PCIU3039304 PCIU3066085 PCIU3060600 ....so the above mentioned data should look like this(see below) after the function has been applied. CRXU1972999 CRXU1972999 (wish to join but in seperate columns) CRXU19739...

2nd search box not sort properly after using 1st search box
I have 2 combo boxes on a continuse form. One to search name and Other to search Department. Private Sub cboFind_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Me.OrderBy = "" Set rs = Me.Recordset.Clone rs.FindFirst "[Last Name] = '" & Me![cboFind] & "' AND [First Name] = '" & Me![cboFind].Column(1) & "'" If Not rs.EOF Then Me.Bookmark = rs.Bookmark Me.cboFind.BackColor = -2147483633 Me.Last_Name.SetFocus End Sub Private Sub cboDepartment_AfterUpdate() ...

Outlook 2000
When I create a new email message, hit the "To" button and select my contact list, I get the Full Name. I would like to get the list Last Name, First Name which is how my "File As" column is setup. Is there a way to do this? Thanks, Randy Wells ...

Sort highest by name
I have 55 teams in A1 to A55. I have their scores in B1 to B55. Is there a way to list the scores (highest to lowest) but giving their team names instead of all the numbers in order of the highest to the lowest? Help Obe 1 Why don't you just sort this data by column B in descending order? Select the range starting from cell B1 then just click the sort descending button on the toolbar. If you don't want to sort the data then you could use a formula. Are there any tie scores? What type of values are the scores? Are they whole numbers? Decimals? -- Biff Microsoft Excel MVP &qu...