Adding Only Visible Cells to Array Concept

Hi Everyone,

I was looking for a solution to add only visible cells to an array and I was able to find one that works.

While it seems simple, I am having trouble grasping the concept of why this solution works so well.

Please Login or Register  to view this content.

Particulary, I want to know why setting a range for Cell 1 and the same range for Cell 2 is able to isolate the desired range.
Why wouldn't one set the cell for cell1 to "C2" and the cell for cell2 to "C208"? I've noticed when I try to test this, the loop appears to never end...

Also, when I print the value of ys(0), ys(etc..), it seems to be all over the place, but the value of a(0), a(etc..) is always the desired visible cells in my list.
How does this work? What is the initial value of ys, and how does the loop end properly?

Thank you

Without seeing the worksheet in question nor the rest of the code...

Please Login or Register  to view this content.

...assigns all visible cells in column C to xs. As to using "C2:C208" twice, it may be that there is a non-continuous break in the data between those cells and the End(xlDown) would not get all the cells in column C. If not, using

Please Login or Register  to view this content.

would work just as well. Also it just may be that a copy of the range was in the clipboard and it was just quicker to write the code...

Originally Posted by mikerickson

There is no cell1, cell2 in your code. Its hard for us to comment on code that we can't see.

Apologies, I will attempt to clarify. By Cell1 and Cell2, I was meaning to refer to the syntax Range(Cell1, [Cell2]).

Originally Posted by mikerickson

Note that someRange(2) is a short-hand version of someRange.Cells(2,1) and does not take filtering or visible cells into account as one increments the 2.

Is this why ys takes on every value from C2 to C208? How is the array only populated with the visible cells? Is it because "xs" is the filtered range and "ys" is the entire range, and the loop searches for matches?

When I comment out the line below and replace it with the similar uncommented line, the code executes and the loop seems to never end. The array still is accurately populated with the 5 visible values as per the filter, but the values of xs(0,1,2, etc) begin with one before the filtered value and continue to the end of the list. I don't understand how the change causes the loop to continue, or how ys, a range that hasn't been initiated, is able to pass the filtered values to the array accurately.

Please Login or Register  to view this content.

Thank you