Cells.SpecialCells: Do you use it much?

bill

Well-known Member
Joined
Mar 7, 2002
Messages
550
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!
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Example - deleting blanks without looping


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

James_B

Active Member
Joined
Jun 11, 2009
Messages
447
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

Code:
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)
 
Last edited:

bill

Well-known Member
Joined
Mar 7, 2002
Messages
550
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

Active Member
Joined
Jun 11, 2009
Messages
447

ADVERTISEMENT

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

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

Well-known Member
Joined
Mar 7, 2002
Messages
550
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,065
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

bill

Well-known Member
Joined
Mar 7, 2002
Messages
550
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,065
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Specialcells is most useful to me with filters and hidden data when you only want to look at the visible cells.
 

bill

Well-known Member
Joined
Mar 7, 2002
Messages
550
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 :(
 

Watch MrExcel Video

Forum statistics

Threads
1,122,369
Messages
5,595,760
Members
414,017
Latest member
surajks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top