VBA Autofilter Excel 2013吗? [英] VBA Autofilter Excel 2013?

问题描述

我一直在使用像这样的行:

I've been using a line like:

Cells.AutoFilter 11, "0"

暂时将第11列自动过滤为"0".

for a while to autofilter column 11 for "0".

我最近更新到Microsoft Office 2013，现在此行出现AutoFilter method of Range class failed运行时错误.这是与Office 2013的兼容性问题还是其他一些问题?

I recently updated to Microsoft Office 2013 and now I'm getting an AutoFilter method of Range class failed runtime error with this line. Is this a compatibility issue with Office 2013 or some other problem?

我应该澄清一下，我已经运行的程序不会出现错误，而是我以前使用过的行，现在对我不起作用.

I should clarify that I am not getting an error with a program I already run, but rather a line which I've used before and is not working for me right now.

代码:

Dim firstRow As Integer
Dim lastRow As Integer
Dim firstCol As Integer
Dim lastCol As Integer
Dim allRange As Range
Dim vRange As Range
Dim bRange As Range
Dim commentsCol As Integer
Dim commentsColRng As Range
Dim fieldNameCol As Integer
Dim userCol As Integer

If Cells(2, 1) <> "" Then

    firstCol = 1
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    firstRow = 2
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row

    commentsCol = Rows(1).find("Comments").Column '11
    fieldNameCol = Rows(1).find("Field Name").Column '8
    userCol = Rows(1).find("User").Column '4

    Set allRange = Range(Cells(firstRow, firstCol), Cells(lastRow, lastCol))
    Set commentsColRng = Range(Cells(firstRow, commentsCol), Cells(lastRow, commentsCol))

    Cells.AutoFilter Field:=1, Criteria1:="PF"
    Cells.AutoFilter commentsCol, "0", xlFilterValues
    Cells.AutoFilter Field:=2, Criteria1:="0", Operator:=xlFilterValues

End If

我有3个自动过滤器，因为我正在尝试多种不同的方法.

I have three autofilters because I'm trying it multiple different ways.

推荐答案

第11列中可能没有任何内容.

You might not have anything in column 11.

例如:

     A         B
---------------------
1  field1   field2
2  a             0 
3  b             1
4  c             2
5  d            23

Sub Test()
    ' this will succeed because column 2 (B) has something
    Cells.AutoFilter 2, "0"

    ' this will fail because column 3 (C) has nothing to filter on
    ' error message will be AutoFilter method of Range class failed
    Cells.AutoFilter 3, "0"
End Sub

这篇关于VBA Autofilter Excel 2013吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！