VBA: Naming a Range

Question

I have searched for a solution but can't seem to find one. If one exists, please point me to it. The question is how do I name a range in VBA.

    wrkSheet.Range("A1").Name = "Test"

Works fine but as soon as I change it to

    wrkSheet.Range("A1:B2").Name = "Test"

gives me problems. Note, wrkSheet is a worksheet object that is defined earlier.

Thanks

"gives me problems" means what exactly? `wrkSheet` is really a string, or is it a worksheet object? If it's a string then you want `Sheets(wrkSheet).Range("A1:B2").Name = "Test"` — Tim Williams, Mar 12 '14 at 18:46

score 1 · Accepted Answer · answered Mar 12 '14 at 18:45

1

you can use this to name a range. Not sure why wrkSheet is a string as it should be a worksheet object

Dim ws As Worksheet
Dim r As Range
Set ws = Sheets(1)
Set r = ws.Range("A1:B2")
r.Name = "test"

answered Mar 12 '14 at 18:45

Sorceri

the code works great!. As a follow up question, I want to have something like r.Name = "test_" & some_string but I am getting a problem. Any ideas? – MathLover Mar 12 '14 at 18:53
Try r.Name = "test_" + some_string – Sorceri Mar 12 '14 at 22:21

score 0 · Answer 2 · answered Mar 12 '14 at 18:42

0

You're not naming a range there... you giving values to a excel range of cells... To name a range you do

range = wrkSheet.Range("A1:B2").Name

Then you can do range(1,1) = "test" for example.

answered Mar 12 '14 at 18:42

Stephenloky

2 Answers2