Weirdness with .SpecialCells(xlCellTypeVisible)

[techturtle]

I have a subroutine (Excel 2010) that includes subtotaling the data on a worksheet, then collapsing the subtotals. With the subtotal on level 2, I am trying to bold all the subtotaled numbers and apply a back color, while leaving the cells in between unbolded and white background. The code that I'm using is this:

Range(Cells(2, 1), Cells(lngLastRow, lngLastCol)).Select
With Selection
    .Font.Bold = False
    .Interior.Color = vbWhite
End With

With Selection
    .SpecialCells(xlCellTypeVisible).Select
    .Font.Bold = True
    .Interior.Color = vbLtBlue
End With

The first selection and With block works as expected; it undoes all bolding and turns everything white. The second With block starts with the same selection, then uses the .SpecialCells(xlCellTypeVisible).select to change it so only the subtotaled rows are selected. In addition to seeing a visible change in the selection, I have verified via selection.cells.count that only those cells are in the selection. However, when the .Font.Bold and .Interior.Color sections execute, they hit everything, not just visible or selected cells. Any idea why this is happening?

 

[VoG]

Possibly

With Selection.SpecialCells(xlCellTypeVisible)
    .Font.Bold = True
    .Interior.Color = vbLtBlue
End With

 

[techturtle]

Possibly

With Selection.SpecialCells(xlCellTypeVisible)
    .Font.Bold = True
    .Interior.Color = vbLtBlue
End With

That did it. I just realized it was doing the same thing in another part of my code as well, so double thanks for this fix. Any idea why it did it that way? I mean, I could prove that only the correct cells were selected, why wouldn't they be the only ones to get the selection-based formatting?

 

[VoG]

I'm not sure why you had that problem but selecting is generally best avoided anyway.

 

[MrKowz]

The problem was because the With Selection creats a temporary range object for that parent selection, and each line is executed based on that range object. If you perform a select inside of the With Selection, it does not overwrite that parent object's range reference.

 

[Jonmo1]

Here's why

With Selection
    .SpecialCells(xlCellTypeVisible).Select
    .Font.Bold = True
    .Interior.Color = vbLtBlue
End With

In the WITH structure, the Red Dots refer to what was originally referenced in the with. REGARDLESS of whatever was slected afterwards.
so even after doing the specialcells(..).select, the with is STILL referring to whatever was selected at the time the With was executed.

Here's a simplified example

Sub Test
 
Range("A1").Select
With Selection
    Range("Z1").Select
    Msgbox .Address 'This will return $A$1
End With
End Sub

 

[techturtle]

Here's why
In the WITH structure, the Red Dots refer to what was originally referenced in the with. REGARDLESS of whatever was slected afterwards.
so even after doing the specialcells(..).select, the with is STILL referring to whatever was selected at the time the With was executed.

Excellent explanation, thank you! Makes sense now why it would do what it did. Even when I was testing the cell count, I was doing it from the Immediate Window, so it was outside the WITH block's special little saved range. And here I always thought that using the the WITH and the dots just saved me some typing...