Why is defined range not working with End(xlDown).Select?

1
votes

I am running a function with a defined range and when trying to use End.(xlDown).Select I get a message "Select method of Range class failed".

I have edited the code below to show only the problem piece. After literally two hours of trying everything, I cannot get it to work.

The annoying part is that I have to use defined ranges since the function is part of a much larger Sub that doesn't work as intended once Select and Activate are used.

Function OutputFunction()

    Dim rng8 As Range
    Set rng8 = ThisWorkbook.Worksheets(5).Range("A2")

    rng1.ClearContents 'Works like a charm.
    rng2.Copy 'No problem here either.

    rng8.End(xlDown).Select 'Fails misserably.
    ActiveCell.Offset(0, 13).Select
    Range(Selection, Range("N3")).Select
    ActiveSheet.Paste
3
excelvba
Unless that sheet is active, you can't select the range. Nor do you really need to. – Rory
Normally it's better to avoid using Select and ActiveCell and ActiveSheet and Activate - see How to avoid using Select in Excel VBA. – BigBen
'Fails misserably. it's probably best comment this year. About your question, rng8 is a range in a different worksheet that ActiveSheet, and that causes an error. You can use Select only in the ActiveSheet. – Foxfire And Burns And Burns
All previous ranges were referencing the same sheet only different ranges. All of them work fine but none of them used Select. The moment Select is used, Excel just goes nuts and starts pasting in a wrong sheet. If I try to specify before code runs "ThisWorkbook.Worksheets(5).Activate" to make sure Excel understands what sheet I'm talking about, the main Sub fails. – vlad_milovanovic

3 Answers

0
votes
    rng2.Copy destination:= rng8.parent.range(rng8.End(xlDown).Offset(0, 13), rng8.parent.Range("N3"))

"After literally two hours of trying everything, I cannot get it to work."

The first rule of Excel Macros: Never, ever, use SELECT in an Excel Macro.

The second rule of Excel Macros: Don't use Select in Excel Macros

The third.....

0
votes

Try:

Option Explicit

Sub test()

    Dim rng8 As Range

    'Have in mind that you refer to a sheet based on it s index, NOT with its name!
    'If sheets order change you will refer to another sheet
    With ThisWorkbook.Worksheets(5)

        Set rng8 = .Range("A2")
        rng8.Select

        .Range(rng8, rng8.End(xlDown)).Select
    End With

End Sub
0
votes

Try to use End(xlDown).Select in my personal macro. First I tested this in the original excel file that I wrote the macro in, and it worked in every step just fine. But the problem occurred when I used it in another file.

After some tests, I changed the .Select with .Activate and it worked. I'm not 100% sure whether we are talking on the same page or not, so tell me if this solved your problem, so I can improve my answer.