In my Column D i have numbers. Normally most of the rows for Col D will have single numbers eg 2 or 12 or 22 and so on. However on some occasion it will have more two or more number eg 2, 4,12. The numbers in the cell are separated by commas.
Example
Col D
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 and row10

KNz wrote:

In my Column D i have numbers. Normally most of the rows for Col D will
have single numbers eg 2 or 12 or 22 and so on. However on some occasion
it will have more two or more number eg 2, 4,12. The numbers in the
cell are separated by commas. Example
Col D
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 and row10

For the cells that just contain 1, e.g. D1 in the example, it's trivial. For
cells that contain multiple values, split it into a temporary array and see
if there's a 1 in any of the array's values.

Sub select1s()
Dim cel As Range, fnd As String, tmp, L0
'just doing Range("D:D") here runs rather slow for me
For Each cel In Range("D1:D" & _
Cells.SpecialCells(xlCellTypeLastCell).Row)
If cel.Value = 1 Then
fnd = fnd & "," & cel.Address
ElseIf InStr(cel.Value, ",") Then
tmp = Split(cel.Value, ",")
For L0 = LBound(tmp) To UBound(tmp)
If tmp(L0) = 1 Then fnd = fnd & "," & cel.Address: Exit For
Next L0
End If
Next cel
'the Mid$() is because 'fnd' will start with a leading ","
If Len(fnd) 0 Then Range(Mid$(fnd, 2)).Select
End Sub

--
Dickens, as you know, never got round to starting his home page.