Run-time error 1004 when trying to resize a multi area range object

[DocAElstein]

Hello barbosajulio77
Welcome to ExcelFox, the thinking man’s excel forum…


I am not 100% sure what your problem is, but possibly the Resize function does not work on a multi area range object. ( I don't know this for sure ** )

Let me explain:

This is your rngDst, A17:C17, E17

Row\Col	A	B	C	D	E	F
16
17	Cell in Area 1	Cell in Area 1	Cell in Area 1		Cell in Area 2
18

Worksheet: STATEMENT

Your range, rngDst , shown above has two areas, as I have shown, A17:C17 is Area item 1 and E17 is Area item 2

I did some experimenting, like in the macros below, and you can see that the first macro, ( which does something similar to your macro at your problem code line), does not work. It gives the same error as you are seeing.
The other 3 macros do work.

The first macro, which errors, is trying to resize a multi area range. The other macros, which do work, are applying the resize function to a single area range object

This first macro will error

Sub DoesNotWork() ' This will error -  run-time error 1004 application-defined or object-defined error
Dim rngDst As Range
 Set rngDst = ActiveSheet.Range("A17:C17, E17")
 Let rngDst.Resize(2, 1).Value = "You will never see this" ' ' This code line will error -  run-time error 1004 application-defined or object-defined error
End Sub

The following 3 macros all work and give the results shown

Sub DoesWork_1()
Dim rngDst As Range
 Set rngDst = ActiveSheet.Range("A17:C17")
 Let rngDst.Resize(2, 1).Value = "Resized Cells of Single Area Range"
End Sub

Row\Col	A	B	C	D
16
17	Resized Cells of Single Area Range
18	Resized Cells of Single Area Range
19

Worksheet: STATEMENT

Sub DoesWork_2()
Dim rngDst As Range
 Set rngDst = ActiveSheet.Range("A17:C17, E17")
 Let rngDst.Areas.Item(1).Resize(2, 1).Value = "Resized Cells of Area 1 of multi area Range"
End Sub

Row\Col	A	B	C	D
16
17	Resized Cells of Area 1 of multi area Range
18	Resized Cells of Area 1 of multi area Range
19

Worksheet: STATEMENT

Sub DoesWork_3()
Dim rngDst As Range
 Set rngDst = ActiveSheet.Range("A17:C17, E17")
 Let rngDst.Areas.Item(2).Resize(2, 1).Value = "Resized Cells of Area 2 of multi area Range"
End Sub

Row\Col	A	B	C	D	E
16
17					Resized Cells of Area 2 of multi area Range
18					Resized Cells of Area 2 of multi area Range
19
20

Worksheet: STATEMENT



** I was not aware that the resize function errors if applied to a multi area range object, so maybe I have learnt something as well from this Thread…
( Note , that as is usual and as is known to me, the resize function works on the top left of the supplied range. So for the first 2 working macros it is resizing cell A17. For the last macro it is resizing cell E17 )


Hope that is some help to you
Alan