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

vishnu
  • 65
  • 6
  • 1
    What is your aim? Why do you want to select the negative cells? Please edit your question to add this important information. Otherwise it is hard to help you. – Ike Apr 05 '22 at 07:53
  • Your code will sort only column B, not A or C. Is that the behavior you need? There is a difference in the length of the code if there are negative values for sure. Please do clarify. – VBasic2008 Apr 05 '22 at 08:02
  • @Ike I want to split the table in to two for further manipulations. In the above output table. first three rows will be considered as one table and next three rows will be considered as different table. programmatically I need to split it. – vishnu Apr 05 '22 at 08:12
  • @VBasic2008 I want to sort all the columns, not only column B. – vishnu Apr 05 '22 at 08:13
  • Do you need selecting only the negative cells in B:B, even if you sort more columns? – FaneDuru Apr 05 '22 at 08:26
  • @FaneDuru My idea is to select the B:B -ve values as table splitting condition. whenever sort is done all columns should be sorted. – vishnu Apr 05 '22 at 08:52

2 Answers2

4

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
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • 1
    Nice, voted up! – FaneDuru Apr 05 '22 at 12:41
  • 1
    Fine solution. - Side note: it may be helpful to point out that each method for determining range boundaries has its own peculiarities, see [Error in finding last used cell in Excel VBA](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) – T.M. Apr 05 '22 at 14:24
  • This also worked. Both solution worked. But I can select only one answer. – vishnu Apr 05 '22 at 16:52
  • 1
    @vishnu Acceptance is indicated by a green colored checkmark next to the answer and marks your preferred answer; on the other hand there is no restriction to upvote any helpful posts with rep>=15 - cf. [Someone answers](https://stackoverflow.com/help/someone-answers) – T.M. Apr 05 '22 at 17:03
2

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
T.M.
  • 9,436
  • 3
  • 33
  • 57