Excel row selection

What do you want to do once you've selected them?

Throw all the rows into a new tab of the worksheet

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

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.

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

That does not scale very well when you have 20,000+ matches

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

Select allOpen in new window

See attached demo workbook.

Cheers,

Dave
findMatch-r1.xls

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!

Glad to be of help!