Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort data in rows to column | Excel Discussion (Misc queries) | |||
sort only rows with data | Excel Programming | |||
Sort data with blank rows dividing data | Excel Discussion (Misc queries) | |||
Sort with blank rows between data rows | Excel Discussion (Misc queries) | |||
How to sort data from rows into columns? | Excel Discussion (Misc queries) |