Microsoft Knowledge Base

• 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).

          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

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

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

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