CODEWITHSUNDEEP
vbaexcel
lisa_rao007
lisa_rao007

Reputation: 357

Combining two ranges

I am trying to combine two ranges into one variable. Here is my code but not sure why I am getting Type Mismatch error 13:

Dim rngText as Range, rngText2 as Range, dText
Set rngText = wSheet3.Range(wSheet3.Range("A1"), wSheet3.Cells(Rows.Count, 1).End(xlUp))
Set rngText2 = wSheet3.Range(wSheet3.Range("B1"), wSheet3.Cells(Rows.Count, 2).End(xlUp))

'this line I am getting an error:
dText = rngText.Value & rngText2.Value

Upvotes: 1

Views: 987

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

The & concatenation operator works with String operands, and you're giving it two 2D Variant arrays containing the values of an unknown number of cells.

You can't use & to combine Range objects like this; the type mismatch is because VBA is expecting String operands to the & operator, therefore resolving the expression to a String.

Instead, use the Application.Union function - that function will take your rngText and rngText2 ranges as arguments, union them, and return a single Range object that includes the two. Because a Range is an object type, you'll need to use the Set keyword for that assignment:

Set dText = Union(rngText, rngText2)

There's not really a reason to not declare dText As Range now :)

Upvotes: 2

Related Questions