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

• 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

          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

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