"John Brock" <jbr***@panix.com> wrote in message news:e61fr7$i64$1@reader1.panix.com...
>I am trying to create a workbook where one of the worksheets contains
> an AutoFiltered table.  It looks like it should be simple -- what
> I am doing is this:
>
>        rng = ws.Range("MyTableRange")
>        rng.AutoFilter()
>
> Unfortunately I get an exception on the second statement, with the
> unhelpful message:
>
> AutoFilter method of Range class failed
>
> The range is rectangular, and contains a legitimate table, with
> column headers in the first row and data in most of the cells.  If
> I create the workbook without calling the AutoFilter statement,
> then manually go to the range in question and set AutoFilter from
> the Excel Data menu, it works as expected.  The worksheet is intended
> to be protected, but protection has not yet been set at the point
> where I try to set AutoFilter.
>
> I Googled for this, and found two other people complaining of the
> same thing, with no answers.  I suspect (and hope!) this means that
> there is an answer -- otherwise there would be many more queries.
>
> Any help would be gratefully appreciated!
> --
> John Brock
> jbr***@panix.com
>

>"John Brock" <jbr***@panix.com> wrote in message news:e61fr7$i64$1@reader1.panix.com...
>>I am trying to create a workbook where one of the worksheets contains
>> an AutoFiltered table.  It looks like it should be simple -- what
>> I am doing is this:
>>
>>        rng = ws.Range("MyTableRange")
>>        rng.AutoFilter()
>>
>> Unfortunately I get an exception on the second statement, with the
>> unhelpful message:
>>
>> AutoFilter method of Range class failed
>>
>> The range is rectangular, and contains a legitimate table, with
>> column headers in the first row and data in most of the cells.  If
>> I create the workbook without calling the AutoFilter statement,
>> then manually go to the range in question and set AutoFilter from
>> the Excel Data menu, it works as expected.  The worksheet is intended
>> to be protected, but protection has not yet been set at the point
>> where I try to set AutoFilter.
>>
>> I Googled for this, and found two other people complaining of the
>> same thing, with no answers.  I suspect (and hope!) this means that
>> there is an answer -- otherwise there would be many more queries.
>>
>> Any help would be gratefully appreciated!
>> --
>> John Brock
>> jbr***@panix.com
>>
>
>

"John Brock" <jbr***@panix.com> wrote in message news:e61oap$kut$1@reader1.panix.com...
> In article <OqpRU#KiGHA.3***@TK2MSFTNGP04.phx.gbl>,
> Ron de Bruin <rondebr***@kabelfoon.nl> wrote:
>>Hi
>>
>>See this page
>>http://www.contextures.com/xlautofilter03.html
>
> The VBA (not VB.NET) example on that page is:
>
> Sub TurnAutoFilterOn()
> 'check for filter, turn on if none exists
>   If Not ActiveSheet.AutoFilterMode Then
>     ActiveSheet.Range("A1").AutoFilter
>   End If
> End Sub
>
> I don't see how this is different than what I am doing now.  My
> 'ws' variable *is* the active worksheet (in fact the only worksheet).
> I tried using a single cell range, as in the example, but that
> makes no difference.  The AutoFilter statement still throws an
> exception.
>
> The example given for turning off AutoFiltering uses an AutoFilterMode
> variable.  I changed my code to:
>
> ws.AutoFilterMode = true
>        rng = ws.Range("MyTableRange")
>        rng.AutoFilter()
>
> But now the first statement throws an exception, with the even less
> enlightening message:
>
> Exception from HRESULT: 0x800A03EC.
>
> There is also a ws.FilterMode, which is read-only, so I can't do
> anything with it, and a ws.EnableAutoFilter variable, which looks
> promising, and which I can successfully set true, but which doesn't
> seem to make any difference.
>
> Come on, *somebody* must have done this!
>
>
>>"John Brock" <jbr***@panix.com> wrote in message news:e61fr7$i64$1@reader1.panix.com...
>>>I am trying to create a workbook where one of the worksheets contains
>>> an AutoFiltered table.  It looks like it should be simple -- what
>>> I am doing is this:
>>>
>>>        rng = ws.Range("MyTableRange")
>>>        rng.AutoFilter()
>>>
>>> Unfortunately I get an exception on the second statement, with the
>>> unhelpful message:
>>>
>>> AutoFilter method of Range class failed
>>>
>>> The range is rectangular, and contains a legitimate table, with
>>> column headers in the first row and data in most of the cells.  If
>>> I create the workbook without calling the AutoFilter statement,
>>> then manually go to the range in question and set AutoFilter from
>>> the Excel Data menu, it works as expected.  The worksheet is intended
>>> to be protected, but protection has not yet been set at the point
>>> where I try to set AutoFilter.
>>>
>>> I Googled for this, and found two other people complaining of the
>>> same thing, with no answers.  I suspect (and hope!) this means that
>>> there is an answer -- otherwise there would be many more queries.
>>>
>>> Any help would be gratefully appreciated!
>>> --
>>> John Brock
>>> jbr***@panix.com
>>>
>>
>>
>
>
> --
> John Brock
> jbr***@panix.com
>

> In article <OqpRU#KiGHA.3***@TK2MSFTNGP04.phx.gbl>,
> Ron de Bruin <rondebr***@kabelfoon.nl> wrote:
> >Hi
> >
> >See this page
> >http://www.contextures.com/xlautofilter03.html
>
> The VBA (not VB.NET) example on that page is:
>
>     Sub TurnAutoFilterOn()
>     'check for filter, turn on if none exists
>       If Not ActiveSheet.AutoFilterMode Then
>         ActiveSheet.Range("A1").AutoFilter
>       End If
>     End Sub
>
> I don't see how this is different than what I am doing now.  My
> 'ws' variable *is* the active worksheet (in fact the only worksheet).
> I tried using a single cell range, as in the example, but that
> makes no difference.  The AutoFilter statement still throws an
> exception.
>
> The example given for turning off AutoFiltering uses an AutoFilterMode
> variable.  I changed my code to:
>
>     ws.AutoFilterMode = true
>         rng = ws.Range("MyTableRange")
>         rng.AutoFilter()
>
> But now the first statement throws an exception, with the even less
> enlightening message:
>
>     Exception from HRESULT: 0x800A03EC.

"John Brock" wrote:

> I am trying to create a workbook where one of the worksheets contains
> an AutoFiltered table.  It looks like it should be simple -- what
> I am doing is this:
>
>         rng = ws.Range("MyTableRange")
>         rng.AutoFilter()
>
> Unfortunately I get an exception on the second statement, with the
> unhelpful message:
>
>     AutoFilter method of Range class failed
>
> The range is rectangular, and contains a legitimate table, with
> column headers in the first row and data in most of the cells.  If
> I create the workbook without calling the AutoFilter statement,
> then manually go to the range in question and set AutoFilter from
> the Excel Data menu, it works as expected.  The worksheet is intended
> to be protected, but protection has not yet been set at the point
> where I try to set AutoFilter.
>
> I Googled for this, and found two other people complaining of the
> same thing, with no answers.  I suspect (and hope!) this means that
> there is an answer -- otherwise there would be many more queries.
>
> Any help would be gratefully appreciated!
> --
> John Brock
> jbr***@panix.com
>
>

"Mike S" wrote:

>
> Jim Thomlinson wrote:
> > Your syntax look a little off...
> >
> >         set rng = ws.Range("MyTableRange")
> >         rng.AutoFilter
> >
>
> No. In VB.NET, the "Set" and "Let" keywords are not supported.
> Therefore, when you are setting a reference to an object, you can't
> type "Set x = y" anymore; instead you type "x = y", same as a normal
> assignment. VB.NET code that uses "Set" or "Let" will not compile;
> however, if you are using the Visual Studio .NET IDE to write your
> code, the IDE will simply delete any "Set"s or "Let"s if you try to
> type them, in order to save you from writing code that won't compile.
>
> --
> Mike S
>
>

>I am trying to create a workbook where one of the worksheets contains
>an AutoFiltered table.  It looks like it should be simple -- what
>I am doing is this:
>
>        rng = ws.Range("MyTableRange")
>        rng.AutoFilter()
>
>Unfortunately I get an exception on the second statement, with the
>unhelpful message:
>
>    AutoFilter method of Range class failed
>
>The range is rectangular, and contains a legitimate table, with
>column headers in the first row and data in most of the cells.  If
>I create the workbook without calling the AutoFilter statement,
>then manually go to the range in question and set AutoFilter from
>the Excel Data menu, it works as expected.  The worksheet is intended
>to be protected, but protection has not yet been set at the point
>where I try to set AutoFilter.
>
>I Googled for this, and found two other people complaining of the
>same thing, with no answers.  I suspect (and hope!) this means that
>there is an answer -- otherwise there would be many more queries.
>
>Any help would be gratefully appreciated!

>It seemed strange to AutoFilter a range on a single worksheet by
>doing something to the entire Application (which might have several
>workbooks open), but it worked!  Can anyone explain the logic to me?
>
>In any case, the code looks like this:
>
>    Dim ws as Microsoft.Office.Interop.Excel.Worksheet
>    ...
>
>    ws.Range("MyTableRange").Activate()
>    ws.Application.Selection.AutoFilter()
>
>Also, does anybody have any idea what the AutoFilter function of
>a Range object does (given that it doesn't turn AutoFiltering on)?