XL: Range Does Not Have Writable Entire Row/Column Property

ID: Q114859

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel 97 for Windows
Microsoft Excel 98 Macintosh Edition

SYMPTOMS

In Microsoft Excel, if you use the EntireRow property in a Microsoft Visual

Basic for Applications procedure to copy an entire row to another row on a worksheet, you receive the following error message:

   Run-time error '1000':
   Range does not have writeable EntireRow property

Note that this behavior also occurs if you use the EntireColumn property to copy an entire column to another column on a worksheet.

CAUSE

This error message occurs because the EntireRow and EntireColumn properties are read-only. You cannot use either property directly, as in the following examples, to change the values in a row or column on a worksheet:

   Sheets("Sheet1").Range("A1").EntireRow = _
       Sheets("Sheet1").Range("A2").EntireRow

   Sheets("Sheet1").Range("A1").EntireColumn = _
       Sheets("Sheet1").Range("B1").EntireColumn

NOTE: In Microsoft Excel 97 and Microsoft Excel 98, you do not receive an error message when you use the EntireRow property as the example illustrates; however, the macro does not copy the row. When you use the EntireColumn as the example illustrates, you receive the following error:

   Run-time error '1004':
   Application-defined or object-defined error

WORKAROUND

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/

To write the contents of one row or column on a worksheet to another row or

column on a worksheet, do either of the following.

Using the Value Property

   Microsoft Excel 5.0, 7.0, 97, and 98:

    - Use the Value property of the EntireRow property as in the following
      example:

         Sheets("Sheet1").Range("A2").EntireRow.Value = _
             Sheets("Sheet1").Range("A1").EntireRow.Value

      This code copies the data from Row 1 to Row 2 of your worksheet.

   Microsoft Excel 97 and 98 ONLY:

    - Use the Value property of the EntireColumn property as in the
      following example:

         Sheets("Sheet1").Range("B1").EntireColumn.Value = _
             Sheets("Sheet1").Range("A1").EntireColumn.Value

      This will copy the data in column A of your worksheet into column B
      of your worksheet.

NOTE: These methods copy only the value of each cell in a row to the cells in another row. Note that in Microsoft Excel 97 only, you can also use these methods to copy the value of each cell in a column to the cells in another column. Formulas, formatting, and cell notes (comments in Microsoft Excel 97) are not copied.

Using Copy and Paste Methods

Use the Copy and Paste methods to copy large ranges of cells on a worksheet, as in the following example:

      ' Substitute Columns for Rows in the following example
      ' to copy information from one column to another.
      Sheets("Sheet1").Rows("2").Copy
      Sheets("Sheet1").Rows("1").PasteSpecial Paste:=xlValues
      Application.CutCopyMode = False

Note that when you use the PasteSpecial method, you can use the Paste argument to specify whether to paste formulas, values, formats, notes or all of these.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

REFERENCES

For more information about the EntireRow Property, click the Search button in Help and type:

    EntireRow Property

For more information about the EntireColumn Property, click the Search button in Help and type:

    EntireColumn Property

Additional query words: 1.00 5.00 5.00a 5.00c 7.00 XL97 XL98 XL7 XL5 err msg

Keywords          : kbprg kbdta kbdtacode PgmOthr KbVBA 
Version           : WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0,98
Platform          : MACINTOSH WINDOWS
Issue type        : kbprb

Last Reviewed: May 17, 1999