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
– Foxfire And Burns And Burns, Commented Aug 12, 2020 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
– Viktor West, Commented Aug 12, 2020 at 10:29
@FoxfireAndBurnsAndBurns Peh that's what I was looking for, thanks! — Albin
– Albin, Commented Aug 12, 2020 at 10:36

Pᴇʜ · Accepted Answer · 2020-08-12 11:14:48Z

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, 2020 at 11:14

answered Aug 12, 2020 at 10:22

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related