By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,916 Members | 1,078 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,916 IT Pros & Developers. It's quick & easy.

Converting VBA to C# for Excel AutoFilter function

P: 5
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:
Expand|Select|Wrap|Line Numbers
  1. Set rData = wsData.Range("BQ5", wsData.Range("BQ65536").End(xlUp))
  2. Set rTargetCl = wsTarget.Range("B65536").End(xlUp).Offset(1,0)
  3. With rData
  4.     .AutoFilter Field:=1, Criteria:=">0", Operator:=xlAnd
  5.     rData.Offset(1,0).SpecialCells(xlCellTypeVisible).Copy Destination:=rTargetCl
  6.     .AutoFilter
  7. End With
  8.  
I've tried this in C# but am getting a Cast exeption on the AutoFilter line:
Expand|Select|Wrap|Line Numbers
  1. rData = (Excel.Range)wsData.get_Range("BQ5", wsData.get_Range("BQ65536", Type.Missing).get_End(Excel.xlDirection.xlUp));
  2. rTargetCl = (Excel.Range)wsTarget.get_Range("B65536", Type.Missing).get_End(Excel.xlDirection.xlUp).get_Offset(1,0);
  3.  
  4. // I get the error on this line
  5. ((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);
  6.  
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.
Oct 20 '06 #1
Share this Question
Share on Google+
1 Reply


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

Like this:
Expand|Select|Wrap|Line Numbers
  1. rData.Autofilter(1, ">0", Excel.XlAutoFilterOperator.xlAnd, Type.Missing, Type.Missing);
  2. rData.get_Offset(1,0).SpecialCells(Excel.XlCellType.xlCellTypeVisible, Type.Missing).Copy(rTargetCl);
  3.  
Voila!
Oct 20 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.