Failed selection on union range

Question

I try to select the range that was union under one variable, but it fails.

Sub filter()
    'Clear prev result table
    Worksheets("main").Range("A3").CurrentRegion.Delete

    'Declare variable
    Dim selectedRows As Range
    Set selectedRows = Worksheets("contacts").Range("A1:B1")


    'Get total used range in contacts database
    numRows = Mid(Worksheets("contacts").UsedRange.Address, 9)
    For Each cell In Worksheets("contacts").Range("B1:B" & numRows)
        If cell.Value = Worksheets("main").Range("B1").Value Then
            'If true, push into array called selectedRows
            Set selectedRows = Application.Union(selectedRows, Worksheets("contacts").Range(cell.Address))
        End If
    Next

    selectedRows.Select '<------ fail here
    Copy Worksheets("main").Range("A3")
End Sub

The macro failed and error message said: Select method or Range class failed. What is wrong with my code?

You should test if `selectedRows = Nothing` before Selecting. Also, is `Worksheets("contacts")` hidden by any chance? One more thought try starting with `Set selectedRows = Worksheets("contacts").Range("A1")` as you test `B1` in your loop and not sure if you can have it in your selected range twice. — Tom, Nov 21 '18 at 09:16
Without too much testing the code worked for me - except that last line which should be `Worksheets("main").Range("A3").Copy`. **Edit:** But I did have the correct sheet selected - the code given by @PawelCzyz should work. — Darren Bartrup-Cook, Nov 21 '18 at 09:43

score 1 · Answer 1 · edited Nov 21 '18 at 11:10

1

You get the error msg because sheet "contacts" is not active. Add this line:

Worksheets("contacts").Activate

Like this:

Sub filter()
    'Clear prev result table
    Worksheets("main").Range("A3").CurrentRegion.Delete

    'Declare variable
    Dim selectedRows As Range
    Set selectedRows = Worksheets("contacts").Range("A1:B1")


    'Get total used range in contacts database
    numRows = Mid(Worksheets("contacts").UsedRange.Address, 9)
    For Each cell In Worksheets("contacts").Range("B1:B" & numRows)
        If cell.Value = Worksheets("main").Range("B1").Value Then
            'If true, push into array called selectedRows
            Set selectedRows = Application.Union(selectedRows, Worksheets("contacts").Range(cell.Address))
        End If
    Next

    Worksheets("contacts").Activate

    selectedRows.Select '<------ fail here

    'this line below is incorrect
    'Copy Worksheets("main").Range("A3")
End Sub

I recommend reading this post: How to avoid using Select in Excel VBA - it will help you improve your code.

edited Nov 21 '18 at 11:10

JohnyL

6,894
3
22
41

answered Nov 21 '18 at 08:09

Pawel Czyz

1,651
4
17
21

still not succesful – Ashraf Misran Nov 21 '18 at 09:03
`Copy Worksheets("main").Range("A3")` should be `Worksheets("main").Range("A3").Copy` – Darren Bartrup-Cook Nov 21 '18 at 09:46
I have edited my answer - like Darren pointed - last line of your macro is incorrect - let me know what you wanted to get done with it. I might be able to correct it as well. – Pawel Czyz Nov 21 '18 at 09:56
Should it be `selectedRows.Copy Worksheets("main").Range("A3")`? – SJR Nov 21 '18 at 12:24

Failed selection on union range

1 Answers1