SpecialCells(xlCellTypeVisible) Not Returning Visible Cells Resulting from AutoFilter

Try

Please Login or Register  to view this content.

That's not what I see.

When I use the Immediate window with

Print myrange.address
I get


$A$11:$A$15,$A$19,$A$23,$A$25:$A$26,$A$28:$A$29,$A$32,$A$39:$A$40,$A$43,$A$49:$A$50,$A$52:$A$55,$A$57,$A$59:$A$60,$A$66:$A$69,$A$72:$A$74,$A$76,$A$84:$A$85,$A$87,$A$89,$A$91:$A$96,$A$99,$A$101:$A$102,$A$104:$A$105,$A$107:$A$108,$A$111:$A$114,$A$116:$A$189

Is this not what you want?

I see the same information as Richard for the returned range. The returned range contains 127 cells in 25 areas.
Your code would only cause an error if the reference was off the sheet.

Consider this, which returns $A$180.

Please Login or Register  to view this content.

But the way you think it should error as A1 only has 1 item and referencing 180 would be out of range. That assumption is incorrect.

This on the other hand would error because it references beyond the last row on the sheet.

Please Login or Register  to view this content.

Very helpful. I think I see what I am doing wrong. To help me better understand, what lines of code would produce the values of the first cell in myRange, and the last cell in myRange (values, not addresses)

What would the code look like that would load the values of the visible cells (myRange) into a dynamic array?

Thanks again...

Please Login or Register  to view this content.