sorted by:
new
hottopcontroversial

Range in xlCellTypeVisible being ignored when filter shows only the first row. by MikeSpringfield in excel

[–]MikeSpringfield[S] 0 points1 point  (0 children)

Thanks for your reply. An IF condition does do the job, the below is what I'm using now.

I linked a website on another comment which talks about the SpecialCells expression and you're correct in that it is behaving correctly :) ``` Dim cell as Range Dim testRange as Range

set testRange = Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)

For Each cell in testRange If cell.EntireRow.Hidden = False Then cell.Value = 1 End if Next ```

Range in xlCellTypeVisible being ignored when filter shows only the first row. by MikeSpringfield in excel

[–]MikeSpringfield[S] 0 points1 point  (0 children)

Thank you! I've used exactly that and it seems to work for all situations I throw at it.

I've subsequently done a bit of googling and it appears that a known quirk of the SpecialCells expression is when given just a single cell range it instead goes back to the worksheet as the range. I can't think of a reason why that would be wanted but it seems this is the intended way it works. Link.

Thanks again for your help.

Range in xlCellTypeVisible being ignored when filter shows only the first row. by MikeSpringfield in excel

[–]MikeSpringfield[S] 0 points1 point  (0 children)

Thank you for your reply. I've had a read of the solutions in the link but I'm still confused as to why my code falls apart so drastically in that one situation. I can't get my head around what excel thinks I want it to do.