Converting VBA to C# for Excel AutoFilter function

[doug9149]

Hello all, I've got some code I wrote using VBA for Excel that I'm trying to recreate using C#.NET. The code autofilters 1 entire column from 1 Worksheet from a Workbook and then appends this filtered code to a new column in a new Worksheet in a new Workbook.

First, the VBA code:

Set rData = wsData.Range("BQ5", wsData.Range("BQ65536").End(xlUp))
Set rTargetCl = wsTarget.Range("B65536").End(xlUp).Offset(1,0)
With rData
    .AutoFilter Field:=1, Criteria:=">0", Operator:=xlAnd
    rData.Offset(1,0).SpecialCells(xlCellTypeVisible).Copy Destination:=rTargetCl
    .AutoFilter
End With
 

I've tried this in C# but am getting a Cast exeption on the AutoFilter line:

rData = (Excel.Range)wsData.get_Range("BQ5", wsData.get_Range("BQ65536", Type.Missing).get_End(Excel.xlDirection.xlUp));
rTargetCl = (Excel.Range)wsTarget.get_Range("B65536", Type.Missing).get_End(Excel.xlDirection.xlUp).get_Offset(1,0);
 
// I get the error on this line
((Excel.Range)rData.AutoFilter(1,">0",Excel.xlAutoFilterOperator.xlAnd, Type.Missing, Type.Missing)).get_Offset(1,0).SpecialCells(Excel.xlCellType.xlCellTypeVisible, Type.Missing).Copy(rTargetCl);
 

I cannot remove the Range cast because if I do I don't get any other options after the final AutoFilter paren. And yet that cast appears to be causing the problem.

Any help would be greatly appreciated.

[doug9149]

I figured it out. I just needed to break it up into two lines.

Like this:

rData.Autofilter(1, ">0", Excel.XlAutoFilterOperator.xlAnd, Type.Missing, Type.Missing);
rData.get_Offset(1,0).SpecialCells(Excel.XlCellType.xlCellTypeVisible, Type.Missing).Copy(rTargetCl);
 

Voila!