Microsoft KB Archive/124220

= Microsoft Knowledge Base =

XL5 Err Msg: Columns (or Rows) Method of Range Class Failed
Last reviewed: September 13, 1996

Article ID: Q124220

The information in this article applies to:


 * Microsoft Excel for Windows, versions 5.0, 5.0c
 * Microsoft Excel for Windows 95, version 7.0
 * Microsoft Excel for the Macintosh, version 5.0
 * Microsoft Excel for Windows NT, version 5.0

SYMPTOMS
When you run a Visual Basic for applications macro in Microsoft Excel, if the macro uses the Columns or the Rows method of the Range class, you may receive either of the following error messages:

Run-time error '1004': Columns Method of Range Class Failed

-or-

Run-time error '1004': Rows Method of Range Class Failed

CAUSE
If the argument passed to the Columns method has a value less than 1, you will receive the above error message. For example, if you run the following sample code, you will receive the error message.

Microsoft provides examples of Visual Basic procedures 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 Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose.

Sample Visual Basic Code
Sub Test 'Passing a value of zero to the Columns method will result in an error Selection.Columns(0).Select End Sub

WORKAROUNDS
Although you cannot use a value less than 1 with the Columns method, you can refer to a specific column with the Offset and Resize methods. To do this, use either of the following methods.

Method 1: To change the current selection to that of a single column of

cells that has the same number of rows as the previous selection (in this case two columns to the left of the left-most column in         the previous selection), use the following code:

Selection.Offset(0, -2).Resize(Selection.Rows.Count, 1).Select

NOTE: To do this with the Columns method, the syntax would be         Selection.Columns(-1).Select (but using -1 with Columns causes          the error message described above). Method 2: To change the selection C1:C4 to the range B1:B4, use the

following code:

Range("C1:C4").Offset(0,-1).Select

NOTE: To do this with the Columns method, the syntax would be         Range("C1:C4").Columns(0).Select (but using 0 with Columns          causes the error message described above). Note that the second argument of the Offset method (-1) is one less than the argument used in the Columns method example (0). Note also that you do not need to use the Resize method, because the original range (C1:C4) is only one column wide.

STATUS
Microsoft has confirmed this to be a problem in the versions of Microsoft Excel listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION
You can use an argument with a value of less than one (1) with the Rows method. Using this value has the effect of referring to a range that is above the range object that is referred to by the Rows method, as in the following example:

Range("A2:C2").Rows(0).Select The above statement causes the current selection to be changed from A2:C2 to A1:C1.

Also note that you WILL receive an error if you use the Rows or Columns methods to create a reference that does not exist, as in the following examples:

Range("A1").Rows(0).Select

-or-

Cells(1,256).Columns(2).Select The above statements generate errors because the resulting references are not valid references in Microsoft Excel (the first example is one row above cell A1 and the second example refers to a cell in column 257--neither of these references exist in Microsoft Excel).

REFERENCES

For more information about Rows or Columns Method, click the Index tab in Microsoft Excel 7.0 Help, type the following text

Rows Method

-or-

Columns Method and then double-click the selected text to go to the "Rows Method" or the "Columns Method" topic.

For more information on the Columns method, choose Contents from the Help menu in Microsoft Excel 5.0, click the Programming With Visual Basic topic, click the Search button, and type the following text:

Columns Method