Run-time error 1004 You can’t change part of an array.

  • Dear Sir / Madam,

    I calculate some values in Spreadsheet A of a Workbook and copy the values only to spreadsheet B in the same workbook using a macro. On the second or subsequent time in which I use the macro to place the newer data in the same location in spreadsheet B by deleting some cells in rows and columns to the left of where the current data is to be stored the macro immediately shows a run-time error. The cells being removed are blank cells with no formulas or values or formatting. The first time I run the macro it works. Upon running it the second time it immediately opens a message which states “Run-time error 1004 You can’t change part of an array”.

    I am running the Excel spreadsheet on Windows 10, Excel 2016.

    None of the following work to solve the problem:

    I cannot find any GWXL97.DLA file;

    Edit Copy and Edit Paste of the highlighted cells into the same location as the cells to be deleted;

    Changing the macro to a different group of unformatted empty cells to remove produces the same error message;

    The trust settings for macros in Excel are open and macros enabled;

    Find & Select, Go to Special and Current Array says that no cells are found;

    Home and Clear Button, Clear Formats has no effect;

    F2, CTRL +X, select new location CTRL +V, CTRL+Shift + enter have no effect.

    Following is the initial code section text where the problem occurs:

    How do I remove the error message or simply bypass it as it has no meaning or use?

    Many thanks in advance for your assistance.

    Best Regards,


  • Please note the forum rules on cross-posting, and supply the relevant links. Thanks.

    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!