sort only rows with data
 

Joel provided code where some cells are empty in the sort range. I read you
as having zeros in some cells, and when you sort ascending the zeros are at
the top, but you want them at the bottom.
Here is a solution for that. This will find the largest number in the range
(MAX), loop through each value and if it = 0, adds MAX + 1. This will make
them the largest numbers and will sort to the bottom of the range. Once
sorted, they will be changed back to zeros.

Sub sortSpecial()
Dim rng As Range, c As Range
Dim MyMax As Double

Set rng = Sheets("Sheet1").Range("V11:V72")
MyMax = WorksheetFunction.Max(rng)

For Each c In rng
If c.Value = 0 Then c.Value = MyMax + 1
Next
rng.Sort Key1:=Range("V11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For Each c In rng
If c.Value = MyMax + 1 Then c.Value = 0
Next
End Sub


Mike F
"usmc-r70" wrote in message
...
I have a spreadsheet with a data range from A11:AR74, with the sort column
being 'V'.

I need a macro button to sort rows 11:74 in assending order for those rows
with data in column 'V' greater than zero, leaving those rows with zero in
column 'V' below the 'populated' rows.

Additionally, I need to disable the manual sort and auto filter features,
leaving the macro button as the only means to sort on this worksheet.

sort only rows with data
 

Mike you are correct, but I should have mentioned that the 'zeros' were the
result of a direct reference to column 'AR' in the corresponding row. I must
restablish the reference (i.e. =AR11, AR12, AR13 ...) the corresponding row.
Can you help?

"Mike Fogleman" wrote:

Joel provided code where some cells are empty in the sort range. I read you
as having zeros in some cells, and when you sort ascending the zeros are at
the top, but you want them at the bottom.
Here is a solution for that. This will find the largest number in the range
(MAX), loop through each value and if it = 0, adds MAX + 1. This will make
them the largest numbers and will sort to the bottom of the range. Once
sorted, they will be changed back to zeros.

Sub sortSpecial()
Dim rng As Range, c As Range
Dim MyMax As Double

Set rng = Sheets("Sheet1").Range("V11:V72")
MyMax = WorksheetFunction.Max(rng)

For Each c In rng
If c.Value = 0 Then c.Value = MyMax + 1
Next
rng.Sort Key1:=Range("V11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For Each c In rng
If c.Value = MyMax + 1 Then c.Value = 0
Next
End Sub


Mike F
"usmc-r70" wrote in message
...
I have a spreadsheet with a data range from A11:AR74, with the sort column
being 'V'.

I need a macro button to sort rows 11:74 in assending order for those rows
with data in column 'V' greater than zero, leaving those rows with zero in
column 'V' below the 'populated' rows.

Additionally, I need to disable the manual sort and auto filter features,
leaving the macro button as the only means to sort on this worksheet.

sort only rows with data
 

Maybe this...
Sub sortSpecial()
Dim rng As Range, c As Range
Dim MyMax As Double

Set rng = Sheets("Sheet1").Range("V11:V72")
MyMax = WorksheetFunction.Max(rng)

For Each c In rng
If c.Value = 0 Then c.Value = MyMax + 1
Next
rng.Sort Key1:=Range("V11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For Each c In rng
If c.Value = MyMax + 1 Then c.Value = 0
Next
Call LastStep
End Sub

Sub LastStep()

Dim redRng As Range
Range("V11").Select
Set redRng = Range("V11:V72")
For Each cell In redRng
If cell.Value < "" Then
ActiveCell.FormulaR1C1 = "=RC[22]"
ActiveCell.Offset(1, 0).Select
End If
Next cell

End Sub


HTH,
Ryan---


"usmc-r70" wrote:

Mike you are correct, but I should have mentioned that the 'zeros' were the
result of a direct reference to column 'AR' in the corresponding row. I must
restablish the reference (i.e. =AR11, AR12, AR13 ...) the corresponding row.
Can you help?

"Mike Fogleman" wrote:

Joel provided code where some cells are empty in the sort range. I read you
as having zeros in some cells, and when you sort ascending the zeros are at
the top, but you want them at the bottom.
Here is a solution for that. This will find the largest number in the range
(MAX), loop through each value and if it = 0, adds MAX + 1. This will make
them the largest numbers and will sort to the bottom of the range. Once
sorted, they will be changed back to zeros.

Sub sortSpecial()
Dim rng As Range, c As Range
Dim MyMax As Double

Set rng = Sheets("Sheet1").Range("V11:V72")
MyMax = WorksheetFunction.Max(rng)

For Each c In rng
If c.Value = 0 Then c.Value = MyMax + 1
Next
rng.Sort Key1:=Range("V11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For Each c In rng
If c.Value = MyMax + 1 Then c.Value = 0
Next
End Sub


Mike F
"usmc-r70" wrote in message
...
I have a spreadsheet with a data range from A11:AR74, with the sort column
being 'V'.

I need a macro button to sort rows 11:74 in assending order for those rows
with data in column 'V' greater than zero, leaving those rows with zero in
column 'V' below the 'populated' rows.

Additionally, I need to disable the manual sort and auto filter features,
leaving the macro button as the only means to sort on this worksheet.

sort only rows with data
 

Maybe you could use a helper column with

=IF(V11=0,99^99,V11) copied down to column74

Sort on that column.


Gord Dibben MS Excel MVP


On Sun, 21 Jun 2009 06:53:01 -0700, usmc-r70
wrote:

Mike you are correct, but I should have mentioned that the 'zeros' were the
result of a direct reference to column 'AR' in the corresponding row. I must
restablish the reference (i.e. =AR11, AR12, AR13 ...) the corresponding row.
Can you help?

"Mike Fogleman" wrote:

Joel provided code where some cells are empty in the sort range. I read you
as having zeros in some cells, and when you sort ascending the zeros are at
the top, but you want them at the bottom.
Here is a solution for that. This will find the largest number in the range
(MAX), loop through each value and if it = 0, adds MAX + 1. This will make
them the largest numbers and will sort to the bottom of the range. Once
sorted, they will be changed back to zeros.

Sub sortSpecial()
Dim rng As Range, c As Range
Dim MyMax As Double

Set rng = Sheets("Sheet1").Range("V11:V72")
MyMax = WorksheetFunction.Max(rng)

For Each c In rng
If c.Value = 0 Then c.Value = MyMax + 1
Next
rng.Sort Key1:=Range("V11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For Each c In rng
If c.Value = MyMax + 1 Then c.Value = 0
Next
End Sub


Mike F
"usmc-r70" wrote in message
...
I have a spreadsheet with a data range from A11:AR74, with the sort column
being 'V'.

I need a macro button to sort rows 11:74 in assending order for those rows
with data in column 'V' greater than zero, leaving those rows with zero in
column 'V' below the 'populated' rows.

Additionally, I need to disable the manual sort and auto filter features,
leaving the macro button as the only means to sort on this worksheet.

sort only rows with data
 

Mike you are correct, but I should have mentioned that the 'zeros' were
the
result of a direct reference to column 'AR' in the corresponding row. I
must
restablish the reference (i.e. =AR11, AR12, AR13 ...) the corresponding
row.

Are you saying that Column V and Column AR are showing the same information?
Or did you mean that Column V has a formula in it and in that formula
somewhere is a reference to Column AR for the same row? In other words, for
Row 11, do you really have the formula =AR11 or is the formula more involved
than that? If you really have = AR11, then Ryan's code should work for you;
otherwise a more involved routine will probably be needed.

--
Rick (MVP - Excel)

sort only rows with data
 

Changed 1 line from = 0 to = formula. This will replace the reference to
column AR for only the zero cells, not all the cells in the range.

Sub sortSpecial()
Dim rng As Range, c As Range
Dim MyMax As Double

Set rng = Sheets("Sheet1").Range("V11:V72")
MyMax = WorksheetFunction.Max(rng)

For Each c In rng
If c.Value = 0 Then c.Value = MyMax + 1
Next
rng.Sort Key1:=Range("V11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For Each c In rng
If c.Value = MyMax + 1 Then c.Formula = "=AR" & c.Row
Next
End Sub

Mike F
"usmc-r70" wrote in message
...
Mike you are correct, but I should have mentioned that the 'zeros' were
the
result of a direct reference to column 'AR' in the corresponding row. I
must
restablish the reference (i.e. =AR11, AR12, AR13 ...) the corresponding
row.
Can you help?

"Mike Fogleman" wrote:

Joel provided code where some cells are empty in the sort range. I read
you
as having zeros in some cells, and when you sort ascending the zeros are
at
the top, but you want them at the bottom.
Here is a solution for that. This will find the largest number in the
range
(MAX), loop through each value and if it = 0, adds MAX + 1. This will
make
them the largest numbers and will sort to the bottom of the range. Once
sorted, they will be changed back to zeros.

Sub sortSpecial()
Dim rng As Range, c As Range
Dim MyMax As Double

Set rng = Sheets("Sheet1").Range("V11:V72")
MyMax = WorksheetFunction.Max(rng)

For Each c In rng
If c.Value = 0 Then c.Value = MyMax + 1
Next
rng.Sort Key1:=Range("V11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For Each c In rng
If c.Value = MyMax + 1 Then c.Value = 0
Next
End Sub


Mike F
"usmc-r70" wrote in message
...
I have a spreadsheet with a data range from A11:AR74, with the sort
column
being 'V'.

I need a macro button to sort rows 11:74 in assending order for those
rows
with data in column 'V' greater than zero, leaving those rows with zero
in
column 'V' below the 'populated' rows.

Additionally, I need to disable the manual sort and auto filter
features,
leaving the macro button as the only means to sort on this worksheet.