Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turn filters off and back on
I'm trying to write a large array to a range on a sheet next to another range
that may have filtered rows. I'm doing this: rng.Cells.set_Value(Type.Missing, (object)arrValues); arrValues is a 2-dimensional array with nulls on the filtered rows (because it is derived from the range it is being pasted next to). When there are no filtered or hidden rows in the adjacent range, this is fast and works fine. But when rows are filtered, the data I write gets scrambled and repeated after the first filtered row. Does anyone know if there is a way to turn the filter off before writing and then turn it back on after? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turn filters off and back on
ALV;7180574 Wrote: I'm trying to write a large array to a range on a sheet next to another range that may have filtered rows. I'm doing this: rng.Cells.set_Value(Type.Missing, (object)arrValues); arrValues is a 2-dimensional array with nulls on the filtered rows (because it is derived from the range it is being pasted next to). When there are no filtered or hidden rows in the adjacent range, this is fast and works fine. But when rows are filtered, the data I write gets scrambled and repeated after the first filtered row. Does anyone know if there is a way to turn the filter off before writing and then turn it back on after? Thanks. This line Code: -------------------- Selection.AutoFilter -------------------- you can use before and after, experiment with it in different places in your code to find the optimum time to turn the filter on and off! Regards, SImon -- Simon Lloyd |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turn filters off and back on
Thanks Simon.
My app is a C# add-in for Excel 2007. The only Selection object I can find is Application.Selection and it doesn't have an AutoFilter method. Could you send the namespace for the Selection object you are referring to? "Simon Lloyd" wrote: ALV;7180574 Wrote: I'm trying to write a large array to a range on a sheet next to another range that may have filtered rows. I'm doing this: rng.Cells.set_Value(Type.Missing, (object)arrValues); arrValues is a 2-dimensional array with nulls on the filtered rows (because it is derived from the range it is being pasted next to). When there are no filtered or hidden rows in the adjacent range, this is fast and works fine. But when rows are filtered, the data I write gets scrambled and repeated after the first filtered row. Does anyone know if there is a way to turn the filter off before writing and then turn it back on after? Thanks. This line Code: -------------------- Selection.AutoFilter -------------------- you can use before and after, experiment with it in different places in your code to find the optimum time to turn the filter on and off! Regards, SImon -- Simon Lloyd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Turn filters on in multiple sheets | Excel Programming | |||
How do i turn the merge cell option back on? | Excel Discussion (Misc queries) | |||
turn an filter off or back to All | Excel Programming | |||
Turn Filters On and Off | Excel Programming | |||
Turn ON Autofilter Filters | Excel Programming |