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:
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.