Setting a Range of Cells Back to Itself

Question

I was just curious if there was an easy way to almost do a cut in page with vba code. Let me explain a bit.

Lets Say we have a NamedRange Called LookUpTableData$A1:$D10 each cell in the range has different data, and by data i mean contents, not datatype.

So lets say i retrieve the range in VBA like this.

CompleteModifiedRange = Range("LookUpTableData")
'Now I make some changes to the data
CompleteModifiedRange.Cells(1,1).Value = Blah
CompleteModifiedRange.Cells(2,1).Value = Blah2

So the real question is, How can i sort of paste the entire range back in place without having to loop through all the cells and set them? Kind of like this.

Set Range("LookUpTableData").Range = CompleteModifiedRange

Is there such a way?

Amusingly, your first line of code shows how to do it, just in reverse (that is, [without `Set`](http://stackoverflow.com/q/3872339/11683)). — GSerg, Sep 08 '16 at 13:59
[**Use `Option Explicit`**](http://stackoverflow.com/documentation/vba/3992/vba-option-keyword/13935/option-explicit#t=201609081401041799404). If `CompleteModifiedRange` was properly declared with a `Range` type, things would have been much less confusing. — Mathieu Guindon, Sep 08 '16 at 14:01

score 3 · Accepted Answer · edited Sep 08 '16 at 14:03

3

You've basically almost answered your own question. Because you didn't use Set, your CompleteModifiedRange variable actually contains an array, and therefore you wouldn't use Cells with it:

Dim CompleteModifiedRange As Variant
CompleteModifiedRange = Range("LookUpTableData").Value
'Now I make some changes to the data
CompleteModifiedRange(1,1).Value = Blah
CompleteModifiedRange(2,1).Value = Blah2
Range("LookUpTableData").Value = CompleteModifiedRange

edited Sep 08 '16 at 14:03

Mathieu Guindon

69,817
8
107
235

answered Sep 08 '16 at 13:58

Rory

32,730
5
32
35

Thanks i tried assigning it directly but wasn't working. This works. Thanks – joeb Sep 08 '16 at 14:48

Setting a Range of Cells Back to Itself

1 Answers1