Enumerating SpecialCells

[davea]

Can anyone help with showing how to enumerate a SpecialCells range using a
counter?

For example: with some text in cells B2, D2 and E5

For each cell in ActiveSheet.UsedRange.SpecialCells(xltextValues)
cell.Font.Bold = True
next cell

works just fine. However,

For i = 1 to ActiveSheet.UsedRange.SpecialCells(xlTextValues)
ActiveSheet.UsedRange.SpecialCells(xlTextValues).Item(i).Font.Bold = True
next i

does not work. That is, it bolds cell B2 but not the others. In fact, if you
printout the rows and columns of the returned cells, then B2, B3 and B4 are
returned by the counter method (and these are the cells that actually get
bolded)!

Is this just a bug with SpecialCells and if so, is there a workaround?

Thank you

 

[davea]

Sorry, the second example that does not work should read:

For i = 1 to ActiveSheet.UsedRange.SpecialCells(xlTextValues).Count
ActiveSheet.UsedRange.SpecialCells(xlTextValues).Item(i).Font.Bold = True
next i

 

[Jim Cone]

You need to loop thru each area in the range.
'--
Sub vert()
Dim i As Long
Dim j As Long
With ActiveSheet.UsedRange.SpecialCells(xlTextValues)
For i = 1 To .Areas.Count
For j = 1 To .Areas(i).Cells.Count
.Areas(i).Cells(j).Font.Bold = True
Next 'j
Next 'i
End With
End Sub
--
Jim Cone
Portland, Oregon USA


"davea"
wrote in message
Sorry, the second example that does not work should read:

For i = 1 to ActiveSheet.UsedRange.SpecialCells(xlTextValues).Count
ActiveSheet.UsedRange.SpecialCells(xlTextValues).Item(i).Font.Bold = True
next i

 

[davea]

Thank you so much Jim

I understood that there was a problem with non-contiguous cells but I didn't
think about areas as a solution.

Thanks again.