XL: Error Using VB to Insert Column in Filtered Range

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:

Keywords : kbprg
Version : WINDOWS:5.0,5.0c,7.0; winnt:5.0
Platform : WINDOWS winnt
Issue type :