CODEWITHSUNDEEP
excelvbanamed-ranges
tbcloud
tbcloud

Reputation: 35

Selecting a Named Range Cell Location EXCEL VBA

Basically I want to select a specific cell that my named range is referencing and not returning the Value. I want to mimic the below, but been having errors with selecting the cell address.

Range("AU8").Select

What I have been trying which hasn't been working.

ThisWorkbook.Names("Root").Select
ThisWorkbook.Names("Root").Cells(1,1).Select

Does anyone know how to fix this?

Upvotes: 0

Views: 1359

Answers (1)

Toddleson
Toddleson

Reputation: 4467

Use the .RefersToRange property of the Name Object. Like so:

ThisWorkbook.Names("Root").RefersToRange.Select

But the .Select method will fail if the sheet is not active. A better method is Application.Goto like so:

Application.Goto ThisWorkbook.Names("Root").RefersToRange

Side Note: .Select is often a pointless and wasteful method that hinders the speed and accuracy of your macro scripts. Instead of relying on .Select and Selection, you can instead remove both, and directly reference the ranges that you want to interact with.

Comparison:

    ThisWorkbook.Activate
    Sheets("Sheet1").Activate
    Range("B2").Select
    Selection = "Input Text"
    
    ThisWorkbook.Sheets("Sheet1").Range("B2") = "Input Text"

The last line does the same thing as the previous 4, but skips the steps of activating the workbook, sheet and range individually, which removes that screen jittering, and speeds up the execution of the script by massive amounts.

Upvotes: 1

Related Questions