 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / June 2004Need help Modifying a Sorting macro
I have a range of dated in a column and want to arrange them i assending order. My problem is if that this range of data is linked t another data input sheet whidh places a 0 in place of no data entry When I use my sort macro it puts all 0 enteries on top. I need the macro to be modified to sort all numbers greater that "0" Can anyone help or offer a suggestion, below is the macro. Sub Macro10() ' ' Macro10 Macro ' Macro recorded 6/19/2004 by USER ' ' ActiveSheet.Unprotect Range("A8:F870").Select Selection.Sort Key1:=Range("A8"), Order1:=xlAscending Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _ DataOption1:=xlSortNormal ActiveSheet.Protect DrawingObjects:=True, Contents:=True Scenarios:=True _ , AllowFiltering:=True End Su -- Message posted from http://www.ExcelForum.com This works on selected cells (variable range) if column A is included Clears zero values from column A which are sorted to the bottom of th list. Simplifies coding which would require copying/pasting zeros t the bottom. This works OK in Excel 97. Code ------------------- '--------------------------------------------- Sub sort_zeros_end() Dim MyRange as Range Dim FromRow As Long Dim ToRow As Long ActiveSheet.Unprotect 'Range("A8:F870").Select ' might not need this '--- Set MyRange = Selection FromRow = MyRange.Rows(1).Row ToRow = MyRange.Rows(MyRange.Rows.Count).Row '-- clear zeros ---------------------------- For r = FromRow To ToRow If ActiveSheet.Cells(r, 1).Value = 0 Then ActiveSheet.Cells(r, 1).ClearContents End If Next '--------------------------------------------- rg = "A" & FromRow MyRange.Sort Key1:=Range(rg), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Selection.Sort Key1:=Range(rg), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom '-- ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub '------------------------------------------------- ------------------- -- Message posted from http://www.ExcelForum.com Good Morning BrianB, Thank you for your help. Can you tell me if this modification will remove any values in th cells? All the cells on this sheet are linked to a data input sheet. All cell on this sheet are protected. Will this make a difference in your adjustment A -- Message posted from http://www.ExcelForum.com Perhaps a formula approach would be workable ? Assuming your source range is A8:F870 in Sheet1 Put in G8: =IF(A8<>0,A8+ROW(A8)/10000,ROW(A8)*10^5) Copy G8 down to G870 (This creates a helper column with arbitrary tie-breaks, for use in the sorted extract we're going to set-up in Sheet2) In Sheet2 ----------- Put in A8: =OFFSET(Sheet1!$A$8,MATCH(SMALL(Sheet1!$G$8:$G$870,ROW($A1)),Sheet1!$G$8:$G$ 870,0)-1,COLUMN()-1) Copy A8 across to F8, then down to F870 Format A8:A870 as dates (For a neater look, suppress "zero" dates from showing via: Tools > Options > View tab > Uncheck "Zero values" > OK) A8:F870 in Sheet2 will return the sorted extract of Sheet1's A8:F870 in ascending order by the dates in col A, but with all the "zeros" thrown to the bottom (lines with zeros will appear in the same relative order that they are in Sheet1) You could then hide away Sheet1 and protect Sheet2 ..  Signature-- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- > I have a range of dated in a column and want to arrange them in > assending order. My problem is if that this range of data is linked to [quoted text clipped - 24 lines] > --- > Message posted from http://www.ExcelForum.com/ Hi Max, Thank you for your help. I ran your formular in the approperate row and cells and came out with an error message. In order to help clarify exactly what I am attempting to do, I have a attachment of the sheets I am working with. If you notice the sheet named YEAR when I press the assending butto all 0's go to the top. If I can change this macro to include greate than 0 it may work. I hope by reviewing this it may better explain my situation. Thanks again A -- Message posted from http://www.ExcelForum.com Um .. there's no link in your post but perhaps you could just post some sample data / description of your set-up > ... I ran your formular in the approperate rows > and cells and came out with an error message. What was the error ? Signature-- Rgds Max xl 97 -- Please respond, in newsgroup xdemechanik <at>yahoo<dot>com --- > Hi Max, > Thank you for your help. I ran your formular in the approperate rows [quoted text clipped - 10 lines] > --- > Message posted from http://www.ExcelForum.com/ Sorry about that. The file was to big. I can't get the entire file t you but here is a sample of what the file looks like. The first column has years that I want the user to be able to press button and see the inventory by assending or decending order. When th assending button is pressed it sorts all 0's enteries to the top and want only cells with years in them to show. Thanks Again Al 2001 2002 Zaca Mesa White reisling Bin 1 Row 6 $22 2001 Chalon White Zinfindel Bin 1 Row 4 $25 2001 Rodney Strong Red Cabernet Savignon Bin 1 Row 7 $30 2000 Murphy Good White Chardoney Bin 1 Row 2 $25 2000 Beringer Red Merlot Bin 1 Row 9 $22 2000 Beringer White Rose Bin 1 Row 10 $22 2000 Beringer Red Bin 1 Row 11 $22 2000 Beringer White Bin 1 Row 12 $22 2000 Beringer Red Bin 1 Row 13 $22 2000 Beringer White Bin 1 Row 14 $22 2000 Beringer Red Bin 1 Row 15 $22 2000 Beringer port Bin 1 Row 16 $22 2000 Beringer rose Bin 1 Row 17 $22 2000 Beringer rose Bin 1 Row 18 $22 2000 Beringer rose Bin 1 Row 19 $22 2000 Beringer rose Bin 1 Row 20 $22 1999 Stags Leap Red Merlot Bin 1 Row 3 $20 1998 Rutherford Red Zinfindel Bin 1 Row 5 $23 1997 Silver Oak Red Cabernet Savignon Bin 1 Row 1 $40 1988 Beringer port Tawney Bin 1 Row 8 $5 -- Message posted from http://www.ExcelForum.com Ok, let's take a small sample set of your inventory below, assumed to be in Sheet1, A8:F13, with the sortkey being col A = Year (Cols B to F will house the rest of the data other than the Year) The sample intentionally contains 2 "typical" lines with "zeros" in A9 and A11 (B9:F9 and B11:F11 may or may not contain data - that's ok) 2002 Zaca Mesa White reisling Bin 1 Row 6 $22 0 2001 Chalon White Zinfindel Bin 1 Row 4 $25 0 2001 Rodney Strong Red Cabernet Savignon Bin 1 Row 7 $30 2000 Murphy Good White Chardoney Bin 1 Row 2 $25 In Sheet1 ----------- Put in G8: =IF(A8<>0,A8+ROW(A8)/10^4,ROW(A8)*10^5) H8: =IF(A8<>0,A8+ROW(A8)/10^4,ROW(A8)/10^5) Select G8:H8 and fill down to H13 G8:G13 and H8:H13 are the helper columns for the ascending and descending sorts by year which we're going to set-up in Sheets 2 and 3 .. ----------- In Sheet2 (For Ascending Sort by Year) ----------- Put in A8: =OFFSET(Sheet1!$A$8,MATCH(SMALL(Sheet1!$G$8:$G$13,ROW($A1)),Sheet1!$G$8:$G$1 3,0)-1,COLUMN()-1) Copy A8 across to F8, then down to F13 A8:F13 will return the ascending sort by year, with "zero" lines thrown to the bottom: 2000 Murphy Good White Chardoney Bin 1 Row 2 $25 2001 Chalon White Zinfindel Bin 1 Row 4 $25 2001 Rodney Strong Red Cabernet Savignon Bin 1 Row 7 $30 2002 Zaca Mesa White reisling Bin 1 Row 6 $22 0 0 ----------- In Sheet3 (For Descending Sort by Year) ----------- Put in A8: =OFFSET(Sheet1!$A$8,MATCH(LARGE(Sheet1!$H$8:$H$13,ROW($A1)),Sheet1!$H$8:$H$1 3,0)-1,COLUMN()-1) Copy A8 across to F8, then down to F13 A8:F13 will return the descending sort by year, again with "zero" lines thrown to the bottom: 2002 Zaca Mesa White reisling Bin 1 Row 6 $22 2001 Rodney Strong Red Cabernet Savignon Bin 1 Row 7 $30 2001 Chalon White Zinfindel Bin 1 Row 4 $25 2000 Murphy Good White Chardoney Bin 1 Row 2 $25 0 0 -- For a neater look in Sheets 2 and 3, suppress "zeros" from showing via: Tools > Options > View tab > Uncheck "Zero values" > OK And to complete the set-up .. you could just hide away Sheet1 (Inventory) and protect & show Sheet2 (Ascending) and Sheet3 (Descending) Just adapt the ranges in the formulas and the formula fills to suit .. Signature-- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- > Sorry about that. The file was to big. I can't get the entire file to > you but here is a sample of what the file looks like. [quoted text clipped - 29 lines] > --- > Message posted from http://www.ExcelForum.com/ |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.