Question Accessing the Excel AutoFilter method

[Keith Howard]

There seems to be some inconsistency between the Excel VBA and the Excel Dot
Net object models. I implemented auto filtering via the following code in
Excel VBA:
Selection.AutoFilter
However, when I tried to do the same thing in Dot Net, via the following
code, the AutoFilter object was not available, causing a compilation error:
Friend Shared Sub gsxxxAutoFilter(ByVal vvwksActive As Worksheet)
vvwksActive.Cells.Select()
vvwksActive.selection 'This does not compile because the selection
method does not exist.
vvwksActive.Application.Selection.autofilter() 'This does not
compile because the autofilter method does not exist, even though I was able
to access the Selection object via the Application object.
End Sub

Does anyone know how to access the AutoFilter method via Dot Net?

Also, I had tried the following code:
Friend Shared Sub gsxxxAutoFilter(ByVal vvwksActive As Worksheet)
vvwksActive.Cells.AutoFilter
End Sub
But this starts AutoFilter’ing from row 1, which is not what I am trying to do. I am trying to apply an AutoFilter starting from Row 7 (which is where my selection is).

I have also tried to create a named range and to apply an AutoFilter thereto
via the following code:
Range("TestRange").AutoFilter
This does not work; it does nothing as far as I can tell. No AutoFilter has
been applied. Any other ideas? (I did not know that the Excel Dot Net
object model was different from the VBA one!)

Thanks in advance.

Keith

 

[JohnH]

Much of these interop objects is not strongly typed, they are type Object, casting them to an appropriate type works. If you have Option Strict off you can call these methods without casting by means of "late binding", but you won't get intellisense help.

Dim sel As Excel.Range = CType(vvwksActive.Application.Selection, Excel.Range)
sel.AutoFilter()