VBA - sort in ascending and descending order (based on if value is positive or negative)

[macroos]

Hi All.

I would like to sort my set of data (contains both negative and positive numbers) based in ascending order (if the values are negative) and in descending order (if the values are positive).
How can I do that?
Below, I was able to sort the data on column E in ascending order.
I need help in sorting in descending order if the values are positive.

Sub Step5()


Dim strDataRange As Range
Dim keyRange As Range
Dim lrow As Long
lrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row

Set strDataRange = Range("B2:L" & lrow)
Set keyRange = Range("E1")
strDataRange.Sort Key1:=keyRange, Order1:=xlAscending

End Sub

 

[Rick Rothstein]

I would like to sort my set of data (contains both negative and positive numbers) based in ascending order (if the values are negative) and in descending order (if the values are positive).

I am not sure what you mean by asscending for negative and descending for positives when you have negatives and positives intermingled. For example, what is the output you want to see for this column of numbers...

4
-6
9
1
2
-4
-1
-7
8
-2

 

[macroos]

I would want to see:

-7
-6
-4
-2
-1
9
8
4
2
1

 

[Rick Rothstein]

You are wanting to sort the values in Column E starting at Row 2, correct? If so, give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub SpecialSort()
  Dim X As Long, Data As Variant, Neg As Object, Pos As Object
  Data = Range("E2", Cells(Rows.Count, "E").End(xlUp))
  Set Neg = CreateObject("System.Collections.ArrayList")
  Set Pos = CreateObject("System.Collections.ArrayList")
  For X = 1 To UBound(Data)
    If Data(X, 1) < 0 Then
      Neg.Add Data(X, 1)
    Else
      Pos.Add Data(X, 1)
    End If
  Next
  Pos.Sort
  Pos.Reverse
  Neg.Sort
  Range("E2", Cells(Rows.Count, "E").End(xlUp)).ClearContents
  Range("E2").Resize(Neg.Count) = Application.Transpose(Neg.ToArray)
  Cells(Rows.Count, "E").End(xlUp).Offset(1).Resize(Pos.Count) = Application.Transpose(Pos.ToArray)
  Set Neg = Nothing
  Set Pos = Nothing
End Sub[/td]
[/tr]
[/table]

 

[macroos]

It works perfectly! Thank you so much!

 

[macroos]

Above, it sorts only the values in E.
If there were data on columns A through D, how can i make it so the whole row will sort based on E?

For example
A B C D E
Jack Jill Neighbor Nice 5
Time Blue Neighbor Mean -2
Apple Good Neighbor Nice 4
Pink Red Neighbor Mean -5

After sorting, it would be this based on E.
A B C D E
Pink Red Neighbor Mean -5
Time Blue Neighbor Mean -2
Jack Jill Neighbor Nice 5
Apple Good Neighbor Nice 4

 

[Rick Rothstein]

Above, it sorts only the values in E.
If there were data on columns A through D, how can i make it so the whole row will sort based on E?

Sorry, I misunderstood your layout. Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub SpecialSort()
  Dim LastRow As Long, FirstPositive As Long
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  ActiveSheet.Sort.SortFields.Clear
  ActiveSheet.Sort.SortFields.Add Key:=Range("E2:E" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  With ActiveSheet.Sort
      .SetRange Range("B2:M" & LastRow)
      .Header = xlNo
      .Orientation = xlTopToBottom
      .Apply
  End With
  FirstPositive = Columns("E").Find("-*", , xlValues, , xlRows, xlPrevious, , , False).Row + 1
  ActiveSheet.Sort.SortFields.Clear
  ActiveSheet.Sort.SortFields.Add Key:=Range("E" & FirstPositive & ":E" & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
  With ActiveSheet.Sort
    .SetRange Range("B" & FirstPositive & ":M" & LastRow)
    .Header = xlNo
    .Orientation = xlTopToBottom
    .Apply
  End With
End Sub[/td]
[/tr]
[/table]

 

[macroos]

This error came out - Run-time error '91: Object variable or With block variable not set

It looks like there's an issue with this line:
FirstPositive = Columns("E").Find("-*", , xlValues, , xlRows, xlPrevious, , , False).Row + 1

 

[Rick Rothstein]

This error came out - Run-time error '91: Object variable or With block variable not set

It looks like there's an issue with this line:
FirstPositive = Columns("E").Find("-*", , xlValues, , xlRows, xlPrevious, , , False).Row + 1

You have numbers in Column E, correct?

How are the numbers getting into the cell?

Is it possible for you to have no negative values in Column E?

Is it possible for you to have no positive values in Column E?

 

[macroos]

Yes, there are positive and negative numbers in Column E.

When I clicked the macro, it sorted all in ascending order and then the error pops up.
Only the negative numbers were sorted the way I wanted.