Microsoft KB Archive/114859

= Range Does Not Have Writable Entire Row/Column Property =

Article ID: 114859

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



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.

<div class="status_section">

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.

<div class="references_section">