No announcement yet.

select rows that has particular number in Col D

  • Filter
  • Time
  • Show
Clear All
new posts

  • select rows that has particular number in Col D

    In my Column D i have numbers. Normally most of the rows for Col D will have single number eg 2 or 12 or 22 and so on. However on some occasion it will have two or more numbers eg 2, 4,12. The numbers in the cell are separated by commas.
    Row1 1
    Row2 4
    Row3 2,12
    Row4 11,1
    Row5 2,1
    Row6 3
    Row7 21
    Row8 1,11,15
    Row9 10,1,9
    Row10 1,16
    How can I select
    all the rows that has in Col D the number 1
    ie in the above example it would select row1,row4,row5,row8,row9 and row10
    Many thks

  • #2
    i would use autofilter. Then use text filter > contains and type a comma as your value.


    • #3
      Hi trunten
      I tried custom filter, equals 1 OR contains 1,
      It seems to be missing some rows. Also critea allowed is only two.
      Would have liked criteria equals = 1 OR contains 1, OR ,1,
      This way it would pick cell with number 1 only, cell which being with 1, and cell which has with ,1
      (Please note the commas - after 1 and before 1)
      Any help appreciated


      • #4
        Please read "forum etiquette" 4b in "Forum Rules".


        • #5

          Naughty cross poster.. ;/

          Not sure why you would just want to select those cells.. if your going to do anything with them.. selecting them is probably the least efficient way of including them..

          Any way.. there is MOST probably a better regex pattern.. but this seems to work..

          Private Sub CommandButton1_Click()
              Dim i As Long, rRngToSelect As Range
              With CreateObject("vbscript.regexp")
                  .Pattern = "(\b[1]\b)"
                  For i = 1 To Range("D" & Rows.Count).End(xlUp).Row
                      If .Execute(Cells(i, 4)).Count > 0 Then
                                      If rRngToSelect Is Nothing Then
                          Set rRngToSelect = Range("D" & i)
                          Set rRngToSelect = Union(rRngToSelect, Range("D" & i))
                      End If
                      End If
                  Next i
              End With
          End Sub
          Valuable Resources:

          snb's Website:

          Smallmans Website: