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:

Output:

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

CodePudding user response：

Focussing on your main issue to select only the negative cells (i.e. after sorting - there are number of answers at SO :-) you might try:

With Sheet1          ' << the sheet's Code(Name)
    Dim lastRow As Long
    lastRow = .Range("B" & .Rows.Count).End(xlUp).Row

    Dim srchRow As Long
    srchRow = .Evaluate("Match(False,B2:B" & lastRow & "<0,0)")
    'Debug.Print srchRow
    
    Dim rng As Range
    Set rng = .Range("B2", .Cells(srchRow, "B"))
    rng.Select
End With

CodePudding user response：

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