CODEWITHSUNDEEP
excelvba
simplycoding
simplycoding

Reputation: 2977

How do I build a string to pass into a Range object?

I have a Range object that I'm using to explicitly call out a Range (usually a single cell) and work with the cell. I'm trying to merge the cell that it's set at, to the cell that's two columns to the right. So in total it would be 3 cells merged. I've been playing around with the Address and Range and Offset objects/functions but can't seem to figure out a way to do this cleanly. In the code below, I'm not sure if I should make merge_rng as a string instead and build the string, or make merge_rng as a range and build that.

Here's some code that I've tried:

Dim site_curr_position As Range
Dim merge_rng As String
Set curr_ws = Worksheets(cal_name)

merge_rng = site_curr_position.Address(0, 0) & ":" & site_curr_position.Address(, 0) + 2 'how do I offset by 2 columns?
curr_ws.Range(merge_rng).Merge

Also tried this 

Dim merge_rng As Range
merge_rng = curr_ws.Range(site_curr_position.Address(0, 0), site_curr_position.Address(0,0).Offset(0, 2)) 'assuming variable is at A1, this would return "A1:A1" still
curr_ws.Range(merge_rng).Merge

Upvotes: 0

Views: 58

Answers (1)

SJR
SJR

Reputation: 23081

You can do it by building a string, but it seems unnecessarily complicated. I'd suggest using Resize instead. Both of these merge A1:C1.

Sub x()

Dim r As Range, s As String

Set r = Range("A1")

'Resize
r.Resize(, 3).Merge

'or build a string
s = r.Address
s = s & ":" & r.Offset(, 2).Address
Range(s).Merge

End Sub

Upvotes: 1

Related Questions