SpecialCells visible oddity

  • First off, appologies if this isn't the right forum for this but I wasn't sure where would be most appropriate.

    Now, onto the strange behaviour of SpecialCells(xlCellTypeVisible). Not sure if this is specific to my version of excel (2016) but if you query SpecialCells(xlCellTypeVisible) on a single cell with now rows or columns anywhere on the sheet it will return just the one cell. However, if there is a column or row anywhere on the sheet is hidden then it gets very weird. For example if I run the following code:

    1. Range("A1").SpecialCells(xlCellTypeVisible).Address

    I get the the following results:

    No rows/columns hidden: $A$1

    Column D hidden: $A:$C,$E:$XFD

    Row 12 hidden: $1:$11,$13:$1048576

    Row 12 & Column D hidden: $A$1:$C$11,$E$1:$XFD$11,$A$13:$C$1048576,$E$13:$XFD$1048576

    Pretty sure nobody would expect that behaviour so if it's more wide spread than just excel 2016 it's probably something that you need to take into account!

    Attached a workbook just to show what I mean.

  • It's always been the case that if you apply specialcells to one cell (which doesn't really make sense), it actually applies to the whole sheet.

    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why