Excel row selection

How do I select all rows which contain the value 401.9 in one of the fields... The field may have 10 different values all comma deliminated but I want every row which has one value.

Data field example -
611.0, 648.40, 611.0, 648.40, 611.0, 648.40, 611.0, 648.40, V25.09, V25.09, V25.09, V25.09
585.3, 401.9, 462

Each row has 30 columns of data.  The example data is one of those columns.
LVL 9
Sean MeyerAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
If not parsed into separate columns and no headers you could use AutoFilter on the one column. Use the custom option for "Contains" and the required value. This would still show row 1 even if does not contain value.

Thanks
Rob
0
 
StephenJRCommented:
What do you want to do once you've selected them?
0
 
Sean MeyerAuthor Commented:
Throw all the rows into a new tab of the worksheet
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Rob HensonFinance AnalystCommented:
Assuming you have the comma separated data parsed into columns, you can use Advanced Filter to find the rows that have a value.

You would need a criteria table with the same column headers as the 30 columns of data. Then in separate rows for each column enter the value that you want to find, eg

Col1     Col2     Col3............Col30
401.9
           401.9
                        401.9
                                           401.9

This is telling the filter to do an OR comparison on the data, ie Col1 = 401.9 OR Col2 =401.9 etc.

Thanks
Rob H
0
 
Sean MeyerAuthor Commented:
Anyway to do the row selection in place without splitting up the data into columns?  75,000 rows don't play nice when trying to do this.
0
 
dlmilleCommented:
Why not just use Excel Find for the value 401.9 (look in values, exact match)?  You can also use FindALL for it to give you each of the matches, then you can select each one to go to that row.

Dave
0
 
Sean MeyerAuthor Commented:
That does not scale very well when you have 20,000+ matches
0
 
dlmilleCommented:
Ok - here's a macro that will select all the rows with that value:

You are prompted for something to find, and it selects all rows with that value on the active sheet.

Here's the code:
 
Sub findNumber()
Dim wks As Worksheet
Dim fRange As Range
Dim firstAddress As String
Dim allFound As Range
Dim myFind As String

    Set wks = ActiveSheet
    
    myFind = InputBox("Enter value to find in active worksheet", "Find Values & Select Rows", 401.9)
    
    Set fRange = wks.Cells.Find(what:=myFind, after:=[A1], LookIn:=xlValues, lookat:=xlWhole)
    If Not fRange Is Nothing Then
        firstAddress = fRange.Address
        Set allFound = fRange
        
        Do
            Set fRange = wks.Cells.Find(what:=myFind, after:=fRange, LookIn:=xlValues, lookat:=xlWhole)
            If Not fRange Is Nothing Then
                Set allFound = Union(allFound, fRange)
            End If
        Loop While Not fRange Is Nothing And firstAddress <> fRange.Address
        
        allFound.EntireRow.Select
        MsgBox "ranges selected"
    Else
        MsgBox "desired result not found on active worksheet"
    End If
    
End Sub

Open in new window


See attached demo workbook.

Cheers,

Dave
findMatch-r1.xls
0
 
Sean MeyerAuthor Commented:
NOW THATS WHAT I AM LOOKING FOR!

In Excel 2010 -- Select the column to be filtered --- Text filter -- insert text with option for  OR / AND clauses.

THANKS!
0
 
Rob HensonFinance AnalystCommented:
Glad to be of help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.