excel vba select negative range

Issue

  1. Sort "Column B" <== done with the following code:
' sorts %change from cell B2 to the end of the line
Range("B2", Range("B2").End(xlDown)).Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlNo

Data:

A B C
1 8 2
2 -2 4
3 3 -1
4 -5 8
5 0 10
6 -33 65

Output:

A B C
6 -33 65
4 -5 8
2 -2 4
5 0 10
3 3 -1
1 8 2
  1. Select negative ranges cells only

Now the cursor will be in cell with value -33 (cell B2).
I need to select the ranges with value -33, -5 and -2. Is there a quick way (builtin formulas) to identify these ranges without evaluating each cell value?

Note: The obtained data will be dynmaic, number of rows is not fixed.

Solution

Select Negative Values in Sorted Column

Option Explicit

Sub Test()
    With ActiveSheet.Range("A1").CurrentRegion
        .Sort Key1:=.Columns(2), Order1:=xlAscending, Header:=xlYes
        With .Columns(2).Resize(.Rows.Count - 1).Offset(1)
            On Error Resume Next
            .Resize(Application.CountIf(.Cells, "<0")).Select
            On Error GoTo 0
        End With
    End With
End Sub

Answered By – VBasic2008

Answer Checked By – David Marino (AngularFixing Volunteer)

Leave a Reply

Your email address will not be published.