View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Auric__ Auric__ is offline
external usenet poster
 
Posts: 538
Default select rows that has particular number in Col D

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.