XL5: Run-Time Error Using Resize Method with Range Object Type

ID: Q123191

5.00 5.00c WINDOWS kbprg kberrmsg

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c

SYMPTOMS

In Microsoft Excel, when you use the Resize method with a range object that has been defined using the Set statement in a Visual Basic procedure, you receive the following error message:

   Run-time error '1004':
   Resize method of Range class failed

CAUSE

This error message appears when you use the Resize method if you omit the rowSize argument, and the range that you are resizing is defined as a Range Object data type. The Resize method requires the rowSize argument when you use it with a range object that is defined using the Set statement.

Note that this error does not occur when you omit the columnSize argument under this condition.

WORKAROUNDS

To avoid receiving this error message, use either of the following methods:

Method 1: Dimension your range object as an Object or Variant data type

          instead of as a Range Object type as in the following example:

          Sub Test()
             Dim MyRange As Object
             Set MyRange = Range("[Book1]Sheet1!A1:B2")
             MyRange.Resize(, 3).Select
          End Sub

Method 2: Use the Count property and the Rows method to determine the

          number of rows that the current range contains, and use this
          value as the rowSize argument in the Resize method. Use this
          method if you don't want to change the number of rows in the
          range, and you don't know how many rows are contained in the
          range:

          Sub Test()
              Dim MyRange As Range
              Set MyRange = Range("[Book1]Sheet1!A1:B2")
              MyRange.Resize(MyRange.Rows.Count, 3).Select
          End Sub

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.

MORE INFORMATION

You can use the Resize method in a Visual Basic procedure to resize a range. This method has two arguments, rowSize and columnSize. The Visual Basic Reference indicates that both arguments are optional, and that the new range will keep the same number of rows or columns if you omit one of the arguments. However, when you resize a range that is defined as a Range Object data type, you receive an error message if you omit the rowSize argument.

REFERENCES

For more information about the Resize Method, choose the Search button in the Visual Basic Reference and type:

   Resize Method

KBCategory: kbprg kberrmsg KBSubcategory:

Additional reference words: 1.00 5.00 5.00c

Version           : 5.00 5.00c
Platform          : WINDOWS

Last Reviewed: September 14, 1996