XL: Error Using VB to Insert Column in Filtered Range

 Last reviewed: September 2, 1997
Article ID: Q133412
The information in this article applies to:
  • Microsoft Excel for Windows, versions 5.0, 5.0c
  • Microsoft Excel for Windows NT, version 5.0
  • Microsoft Excel for Windows 95, version 7.0

SYMPTOMS

In Microsoft Excel, when you use Visual Basic code to insert a column in a filtered range, you will receive the following error message: 

   Insert Method of Range Class Failed
Note that you do not receive an error if you insert a column manually if the selected column is the first column in the filtered range.

CAUSE

This error message occurs if you use either of the following Visual Basic Commands: 

   Selection.Entirecolumn.Insert

   -or-

   Selection.Insert Shift:=xlToRight
NOTE: If you turn on the macro recorder when you insert a column in a filtered range, the second command will be recorded. This problem occurs when you try to play back the recorded macro.

WORKAROUND

To avoid this error message, remove the filter criteria from the list (that is, show all records). The insert commands will work if AutoFilter is on as long as all records are visible.

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.

REFERENCES

For more information about Filtering A List Using AutoFilter, choose the Search button in Help and type: 

   filtering lists

Additional query words: 7.00 5.00
Keywords : kbprg PgmOthr kbcode
Version : 5.00 5.00c 7.00
Platform : WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 2, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.