Range Does Not Have Writable Entire Row/Column Property (114859)


The information in this article applies to:

  • Microsoft Excel 97 for Windows
  • Microsoft Excel 98 Macintosh Edition
This article was previously published under Q114859

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. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. 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. This problem no longer occurs in Microsoft Excel 2000.

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

Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbdtacode kberrmsg kbprb kbProgramming KB114859
©2004 Microsoft Corporation. All rights reserved.