Need help Modifying a Sorting macro

[marvontherim]

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

 

[BrianB]

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
'-------------------------------------------------

 

[marvontherim]

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

 

[Max]

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 ..

 

[marvontherim]

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

 

[Max]

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 ?

 

[marvontherim]

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

 

[Max]

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 ..