Announcement

Collapse
No announcement yet.

select rows that has particular number in Col D

Collapse
X
  • 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.
    Example
    ColD
    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
    KNz


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

    Comment


    • #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

      Comment


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

        Comment


        • #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..

          Code:
          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)
                      Else
                          Set rRngToSelect = Union(rRngToSelect, Range("D" & i))
                      End If
                      End If
                  Next i
              End With
          rRngToSelect.Select
          End Sub
          Valuable Resources:

          snb's Website:
          http://www.snb-vba.eu/index_en.html

          Smallmans Website:
          http://www.thesmallman.com/

          Comment

          Working...
          X