Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 14th 18, 11:12 AM posted to microsoft.public.excel.programming
KNz KNz is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2018
Posts: 1
Default 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   Report Post  
Old June 14th 18, 04:56 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 502
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") 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
'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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Select Large Number of Rows Mike H. Excel Discussion (Misc queries) 5 November 5th 07 11:56 PM
Using VBA to select dynamic number of rows Chad81 Excel Programming 3 November 20th 06 07:37 PM
Select number of rows in a graph? cratediggah Excel Programming 0 January 11th 06 03:08 PM
select block of rows w/data between blank rows Janna Excel Programming 6 February 13th 05 02:45 AM
Unable to select rows in the repeat rows on top option Noppie Excel Discussion (Misc queries) 2 December 28th 04 03:17 PM


All times are GMT +1. The time now is 03:21 PM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017