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

score 2 · Answer 1 · answered Oct 02 '13 at 18:14

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 02 '13 at 18:14

Kazimierz Jawor

18,861
7
35
55

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. – Automate This Oct 02 '13 at 18:16
Yes... I need all rows in file 2 to be selected... so I can copy/paste to another sheet.. Thank`s – Art-of-Life Oct 02 '13 at 18:44
1004 is the error number.. One other thing... when I try the script it it select the all numbers in File2. – Art-of-Life Oct 03 '13 at 09:41
To be same as picture you post I put col A from sheet "File1" to sheet "File2" so both column`s are at same sheet.. Now I have only sheet "File2". Can you correct the script to work on same sheet..? I think it`s much easier.. – Art-of-Life Oct 03 '13 at 09:44
you need to change all sections `Sheets("name here")` to the same name inside qutoations – Kazimierz Jawor Oct 03 '13 at 11:25

score 1 · Answer 2 · edited May 23 '17 at 11:57

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 '17 at 11:57

Community

1
1

answered Oct 02 '13 at 18:07

Aaron Thomas

5,054
8
43
89

It say compile error at Dim first_range As Boolean: first_range = True – Art-of-Life Oct 02 '13 at 21:30
I add the names of the files and sheets... But still no luck – Art-of-Life Oct 02 '13 at 21:39
Endless loop My processor is overheating.. File2 is over 32 MB of Data – Art-of-Life Oct 03 '13 at 01:41
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). – Aaron Thomas Oct 03 '13 at 11:39

Select rows that match each item from array

2 Answers2