none
Issue with dynamic name range RRS feed

  • Question

  • Hi All,

    I am creating dynamic name range ('Rng") using below formula.

    =OFFSET('Sheet1'!$A$10,0,0,COUNTA('Sheet1'!$A$10:$A$10000),1)

    Once name is created i am clearing all its contents using below code

    sheet1.range("Rng").clearContents.

    So far everything is good but when i want to copy a range to my dynamic name range than it throws error 1004

    sheet1.range("Rng").VALUE= sheet2.range("CopyRng").VALUE

    I think the issue is that when i clear all the contents from my dynamic range it becomes emyty.

    Thanks,

    Zav

    Friday, April 1, 2016 7:51 PM

Answers

  • I think the issue is that when i clear all the contents from my dynamic range it becomes empty.

    Hello Zav,

    That is correct so simply reference the first cell of the range for the destination.

    The following resizes the first cell of the destination range to the same size as the source data and copies the values.

    Sheet1.Range("A10").Resize(Sheet2.Range("CopyRng").Rows.Count, _
                Sheet2.Range("CopyRng").Columns.Count) _
                = Sheet2.Range("CopyRng").Value

    The following is simply a Copy and PasteSpecial to the first cell of the destination. You could just copy and paste but if the source contains formulas then the formulas will be pasted instead of just the values.

    Sheet2.Range("CopyRng").Copy
    Sheet1.Range("A10").PasteSpecial xlPasteValues


    Regards, OssieMac

    Saturday, April 2, 2016 10:38 AM