Remember Me?

#1
June 14th 18, 11:12 AM posted to microsoft.public.excel.programming
 external usenet poster First recorded activity by ExcelBanter: Jun 2018 Posts: 1
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 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

#2
June 14th 18, 04:56 PM posted to microsoft.public.excel.programming
 external usenet poster First recorded activity by ExcelBanter: Oct 2003 Posts: 506
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") here runs rather slow for me
For Each cel In Range("D1" & _
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
If Len(fnd) 0 Then Range(Mid\$(fnd, 2)).Select
End Sub

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Mike H. Excel Discussion (Misc queries) 5 November 5th 07 11:56 PM Chad81 Excel Programming 3 November 20th 06 07:37 PM cratediggah Excel Programming 0 January 11th 06 03:08 PM Janna Excel Programming 6 February 13th 05 02:45 AM Noppie Excel Discussion (Misc queries) 2 December 28th 04 03:17 PM

All times are GMT +1. The time now is 06:26 PM.