Select rows that match each item from array

Question

In Excel file1, I have very big table, with numbers in each row in same column (let's say col F). In Excel file2, I have numbers also in one column (let's say col A).

Q: How I can select all rows in file2 that contain numbers from file1 col A.

I found how to select rows in file2 that contain one string from file1... but array of strings is a little bit tricky for me and the array in file1 is very big.

Sub SelectManyRows()
Dim CatchPhrase As String
Dim WholeRange As String
Dim AnyCell As Object
Dim RowsToSelect As String

CatchPhrase = "10044" // <- here should be array from file1 col A
'first undo any current highlighting
Selection.SpecialCells(xlCellTypeLastCell).Select
WholeRange = "A1:" & ActiveCell.Address
Range(WholeRange).Select
On Error Resume Next ' ignore errors
For Each AnyCell In Selection
    If InStr(UCase$(AnyCell.Text), UCase$(CatchPhrase)) Then
        If RowsToSelect <> "" Then
            RowsToSelect = RowsToSelect & "," ' add group separator
        End If
        RowsToSelect = RowsToSelect & Trim$(Str$(AnyCell.Row)) & ":" & Trim$(Str$(AnyCell.Row))
    End If
Next
On Error GoTo 0 ' clear error 'trap'
Range(RowsToSelect).Select
End Sub

Kazimierz Jawor · Accepted Answer · 2013-10-02 18:14:10Z

2

The following idea is trying to avoid looping which is usually inefficient. Instead, I used AdvancedFilter assuming its possible with the set of data you have.

The code works fine for the following set of data located in different sheets (File1 and File2). You would need to change it to work with workbooks as you need.

enter image description here

Sub qTest()

    Sheets("File1").Activate
    Dim sRNG As Range
    Dim aRNG As Range

    Set sRNG = Sheets("File2").Range("S1", Sheets("File2").Range("S1").End(xlDown))
    Set aRNG = Sheets("File1").Range("A1", Sheets("File1").Range("a1").End(xlDown))

    aRNG.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=sRNG, Unique:=False

    Dim aADD As String
    aADD = aRNG.SpecialCells(xlCellTypeVisible).Address

    aRNG.Parent.ShowAllData

    Range(aADD).Select

End Sub

answered Oct 2, 2013 at 18:14

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Automate This Over a year ago

Wouldn't have thought of using the advanced filter across sheets like that. Nice job! You might want to add Range(aADD).EntireRow.Select since the OP asked for whole row.

Art-of-Life Over a year ago

Yes... I need all rows in file 2 to be selected... so I can copy/paste to another sheet.. Thank`s

Art-of-Life Over a year ago

1004 is the error number.. One other thing... when I try the script it it select the all numbers in File2.

Art-of-Life Over a year ago

To be same as picture you post I put col A from sheet "File1" to sheet "File2" so both columns are at same sheet.. Now I have only sheet "File2". Can you correct the script to work on same sheet..? I think its much easier..

Kazimierz Jawor Over a year ago

you need to change all sections Sheets("name here") to the same name inside qutoations

Community · Accepted Answer · 2017-05-23 11:57:32Z

1

Something akin to this could be used. Select is avoided, except to actually select the rows you're looking for. Also this dynamically adds the same numbers to a range to be selected at the end.

Dim cl As Variant
Dim first_range As Boolean: first_range = True
Dim colF_range As Range, selected_range As Range


'colF_range is the list in File 2
Set colF_range = Workbooks("File2").Worksheets("Your_Worksheet") _
.Range("F:F")

'Go through each cell in the File 2 list
For Each cl In colF_range
  'Look if that cell's value matches something
  'in File 1 column A
  If Not Workbooks("File1").Worksheets("Your_Worksheet") _
  .Range("A:A").Find(cl.Value) Is Nothing Then
    'If so, select that row in File 2
    If first_range Then
      Set selected_range = cl.EntireRow
      first_range = False
    Else
      Set selected_range = Application.Union _
      (cl.EntireRow, selected_range)
    End If
  End If
Next

edited May 23, 2017 at 11:57

CommunityBot

11 silver badge

answered Oct 2, 2013 at 18:07

Aaron Thomas

5,3418 gold badges52 silver badges94 bronze badges

4 Comments

Art-of-Life Over a year ago

It say compile error at Dim first_range As Boolean: first_range = True

Art-of-Life Over a year ago

I add the names of the files and sheets... But still no luck

Art-of-Life Over a year ago

Endless loop My processor is overheating.. File2 is over 32 MB of Data

Aaron Thomas Over a year ago

This is just the framework - you need to adjust the names of the files and sheets, and change the range of colF_range. Put in literally it will go through all cells in column F, even the empty ones (possibly over 1 million rows).

Collectives™ on Stack Overflow

Select rows that match each item from array

2 Answers 2

5 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related