Cells.SpecialCells: Do you use it much?

[bill]

Hello,
Saw this in some of the threads here on MrExcel and I don't have any familiarity with it.

Do you find yourself using it a lot and if so, how does it make your life/coding easier?

thx!

 

[VoG]

Example - deleting blanks without looping

Sub DelBlanks()
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[James_B]

Personally, yes I find it very helpful. Suppose, for instance, I want to replace all references to a sheet called CDSData1 with CDSData2. I just do

SheetX.UsedRange.Cells.SpecialCells(xlCellTypeFormula).Replace _
   What:="CDSData1!", Replacement:="CDSData2!",  LookAt:=xlPart

which avoids the danger of altering actual values and is much quicker than looping and searching for a leading "=" and so on. (Apologies if some of the above syntax is wrong; I don't have Excel to hand)

 

[bill]

hmmm - i suppose I'm so set in my ways (15+ years of excel/vba) that I'm having a hard time really conceiving of how I'd use it.

Would REALLY like some more practical examples if you can be so gracious to provide

 

[James_B]

Suppose you have a load of data with some holes in it, and you want to set each of those holes equal to the cell above it. I can't test this, but I think something like the following would do it (very quickly and without looping):

set rBlanks = Activesheet.UsedRange.SpecialCells(xlCellTypeBlanks)
if not rBlanks is nothing then
   rBlanks.Formula = "=" & rBlanks(1).Offset(-1).Address
   rBlanks.Value = rBlanks.Value
end if

...though this may need some jigging around.

 

[bill]

I am playing with it now... I have a RANGE defined as:
set titleRange = sheet1.range("$A$1:$AK$1")

and I want to find the last cell in the RANGE , so I do :

set yaryar = titleRange.SpecialCells(xlCellTypeLastCell)

but it acts like USEDRANGE and makes yaryar (catchy eh?): $AS$203
(I expected: $AK$1)

What am I missing?

sure, I can do this: titleRange.End(xlToRight)
(but thought i could do something a tad more slick!)

 

[RoryA]

xlCellTypeLastCell finds the last cell in the usedrange of the sheet, not just the range you specify.

 

[bill]

regards usedrange, i suppose i suspected as much...

i dunno... just seems like SPECIALCELLS is half-baked to me...

There are so many multi-part operations I perform to target a particular cell that SPECIALCELLS, at least when I 1st looked, was to be my savior.

my bad for assuming

 

[RoryA]

Specialcells is most useful to me with filters and hidden data when you only want to look at the visible cells.

 

[bill]

How well does SPECIALCELLS play with AUTOFILTER ?

Also, I tried this:
Set yaryar = titleRange.Offset(1).Resize(maxRow, 1).SpecialCells(xlCellTypeFormulas, xlErrors)

to target ERROR cells and rather than return NOTHING (if no cells were found), I received an error message - that wasn't fun.
Again, making assumptions about how it (imho) "should" work