Help! - Range = Array when ranges is filtered is not working

Hello, would really appreciate some help with this please.

I have a large sheet of data in which I would to manipulate a range of data quickly.

To do this I have been copying the range into an array, making the appropriate changes and then dumping the array back to the range.

The reason I have been doing this instead of dealing with the range directly is this way is a lot quicker.

However I have just come across a problem.

For this post, if we can look at a sheet that has...

Capture.PNG

As you can see the range has a filter on it, if we select A2 and change the filter to everything apart from D and E, and the run the following code...

Please Login or Register  to view this content.

It loads the data into the array fine, and it manipulates the data fine, but when it drops the data back onto the sheet, the only information that is correct is in rows 3 to 5.

It seems to work fine until it hits a hidden row, and then everything else equals only the first element of the array.

I would really appreciate some assistance with this please.

No, still no luck.

Thanks for the suggestion though.

Here you go, sorry.

The workbook is designed so that you can right click on a cell in column C and it will toggle the value there. You can right click either a single cell or a selected range (if it is in Column C only).

So if we right click on C7 to make a Y show for Cindy,
Change the filter to exclude Fred from view,
And then Select Range C1:C7 and right click in that range,

This should toggle each cell in the selected range, but does not.

Sample Issue.xlsm

I hope I have made this clear.

Thanks for checking this out for me

Hi gsmcconville,
Do not look at your file. If the filter is applied on the sheet, the values ​​are transferred to an array incorrectly, so try something like this

Please Login or Register  to view this content.

Hi Nilem, the values are loading into the array fine, I have placed a watch on the array and confirm all the values are correct. It is just when dumping the array back to the spreadsheet that it all goes wrong.

Hi gsmcconville,
maybe something like

Please Login or Register  to view this content.