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

Question

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

Unless that sheet is active, you can't select the range. Nor do you really need to. — Rory, Jun 07 '19 at 12:53
Normally it's better to avoid using `Select` and `ActiveCell` and `ActiveSheet` and `Activate` - see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Jun 07 '19 at 12:53
`'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, Jun 07 '19 at 13:03
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, Jun 07 '19 at 14:24

score 0 · Answer 1 · answered Jun 07 '19 at 12:58

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

Harassed Dad · Accepted Answer · 2019-06-07T14:31:43.230

0

    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.....

edited Jun 07 '19 at 14:31

answered Jun 07 '19 at 14:25

Harassed Dad

4,669
1
10
12

Thanks Harassed Dad :) And just out of curiosity since I'm copying in this instance A1:C1 (rng2), then navigating to rng8 (A2) and going all the way down, and then moving 13 columns to the right, what would it look like if I wanted to copy the A1:C1 all the way from below (say N60) all the way up to N3? – vlad_milovanovic Jun 07 '19 at 15:31
destination would be rng8.parent.range("N3:" & mg8.parent.cells(cells.count,13).end(xlup).address) Note I'm only using mg8.parent becasue I don't know the name of your sheet - otherwise I'd use worksheets("name") – Harassed Dad Jun 10 '19 at 15:22

score 0 · Answer 3 · edited Feb 01 '21 at 13:12

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.

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

3 Answers3