So simple but it just doesn't work!

[Peterfallen]

I'm new to Macros and working with a training tutorial, and there's a really simple problem, I just cannot figure out. I feel so stupid asking but i need help.

Here's my code.

So... I copied and pasted the line currently ' - which works fine
But when I type the line, I get an "Unable to get SpecialCells property of the Range class" error.

Please help me.



Sub get_blanks()


Selection.CurrentRegion.Select


Selection.SpecialCells(x1CellTypeBlanks).Select
'Selection.SpecialCells(xlCellTypeBlanks).Select




End Sub

 

[Fluff]

Hi & welcome to MrExcel
Your first line has X1 (number 1) rather than Xl (lower case L)

 

[Peterfallen]

OMG.... thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Rick Rothstein]

OMG.... thank you!

By the way, since you are apparently typing the SpecialCells constants long-hand, you can save yourself some typing by omitting "CellType" text within the constant's name. These will both will cause the SpecialCells function to produce the same result...

Selection.SpecialCells(xlCellTypeBlanks).Select

Selection.SpecialCells(xlBlanks).Select

You can also omit the "CellType" text from each of the other possible SpecialCells arguments as well.

 

[Peterfallen]

Thanks again... so many tricks to learn!

 

[MARK858]

Or if being really lazy..

Selection.SpecialCells(4).Select

 

[Fluff]

And you can find out more about Specialcells, including the Number equivalents here http://excelmatters.com/2015/06/02/super-secret-specialcells/

 

[Rick Rothstein]

Or if being really lazy..

Selection.SpecialCells(4).Select

Personally, I hate "magic numbers" as they can be hard to remember or figure out 6 months from now when you need to modify the code for some reason.

 

[MARK858]

Hi Rick, personally I don't treat them as "magic numbers" but rather just enumeration, but that is probably because I am so used to using specialcells.

As I stated it is lazy but so is using the bracketed version of evaluate which seems commonplace now (not saying you do this) especially when just referring to a range, I also think the same with resizing to the last row rather than directly referring to the range using last row.

If I am coding for myself I have no problem remembering or figuring out 6 months from now what specialcells(4) or specialcells(12) mean and are doing but it is what you are used to.

I am more concerned that the code doesn't have anything to handle the situation if there are no blank cells.

As with most things to do with VBA (and you have implied) there is a lot down to personal taste and also whether you are teaching someone something they are unfamiliar with.