Select referenced range from non-active table via VBA in Excel

Question

Given a certain range rngRange e.g. set rngRange = Workbooks(2).Worksheets(5).Range("C4:P49") If I use rngRange.select it will throw an error message if the table containing the range is not the active window/workbook/table (e.g. by wbWorkbook.Activate and wsWorsheet.Activate). Using rngRange.Activate by itself doesn't work (throws an error), probable a simple problem. But somehow I'm blind today.

Is it possible to "activate" the range directly without activating the workbook/worksheet first? And if not, can I get a workbook/worksheet reference from the range reference somehow (note, the whole thing is inside a function that only gets the range reference, I would like to avoid to add a wbWorkbook/worksheet reference since I have to change all function calls as well)?

`rngRange.Parent` will reference the worksheet where `rngRange` is. And `rngRange.Parent.Parent` will reference the workbook. — Foxfire And Burns And Burns, Aug 12 '20 at 10:22
You might try the Application.Goto Workbooks(2).Sheets(5).Range("C4:P49") to select (or with the range Application.Goto rngRange ) — Viktor West, Aug 12 '20 at 10:29
@FoxfireAndBurnsAndBurns Peh that's what I was looking for, thanks! — Albin, Aug 12 '20 at 10:36

Pᴇʜ · Accepted Answer · 2020-08-12T11:14:48.157

2

rngRange.Parent 'gives you the sheet
rngRange.Parent.Parent 'gives you the workbook

So you can use them to .Activate them.

rngRange.Parent.Parent.Activate
rngRange.Parent.Activate
rngRange.Select

Alternatively as @Viktor mentioned:

Application.Goto rngRange

edited Aug 12 '20 at 11:14

answered Aug 12 '20 at 10:22

Pᴇʜ

56,719
10
49
73

Select referenced range from non-active table via VBA in Excel

1 Answers1

Linked