Is there a way to find on which category of specialcells the activecell falls into?

[prabha_friend]

If not for activecell, for any other cell may be... Possible?

Like cell.type?

 

[Rx_]

Answer: NO not efficiently.
Guess a case statement in a loop could test a cell (actually a range) for all cases of SpecialCells and then count the cells and report back. But, that is not what SpecialCells was designed to do.

So you want to identify if a cell falls into a SpecialCells - then maybe use that information to effect change?
B.T.W. Excel has a limit of 8192 selection areas for SpecialCells.
The common use would include:
Worksheets("Sheet1").Activate
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate

One of the best sites that I have used for Special Cells is at:
http://dmcritchie.mvps.org/excel/proper.htm

Object code is listed to locate and highlight one type of specialcells at a time. Then it presents the list that can be substituted.
It is impressive where an example that took 360 seconds to format a page of data only took a fraction of a second using the SpecialCells code.

However, many, many restrictions apply. It is my suggestion that the entire article is read before attemting to put it into production.

The penality for violation of the rules can be deleting all of the data on the worksheet. This includes exceeding a max number of ranges.


e.g.

[B]Sub ColorAllFormulae()[/B]
 
  ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas).Interior.ColorIndex  = 6
[B]End Sub[/B]
' Or find specialcells like a blank and select them  xlCellTypeBlanks is a constant
[B]Sub SelectAllBlanks()[/B]    
ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Select
[B]End Sub[/B]
 
[B]Sub SelectAllBlanks2()[/B]   
 Range("A1").SpecialCells(xlCellTypeBlanks).Select 
[B]End Sub[/B]

It is probably worth reading this first. If the SpecialCells is checking too many areas, it has a tendency to delte all data on the worksheet.
http://www.rondebruin.nl/win/s4/win003.htm

Couldn't find a formula that returns the type. It might reqiure a case statement for each of the types.

 

[Brianwarnock]

I can't help wondering what Prabha is trying to do? I had hoped that he would come back but although he has visited the forum he has not added to this thread.

Brian

 

[prabha_friend]

Thanks to your reply Rx. Brian. It's not like that. I was travelling when I saw the reply thats why couldn't reply. Anyway I was just trying find the category of that cell. Oh Sorry. I forgot! Merry Christmas to all...

 

[Rx_]

After a short internet search, I don't beleive you can poll a cell (range) and get a single category. That is most likely because a cell can have many categories applied to it.
You can poll the cell for a specific category (e.g. xlCellTypeBlanks )
These can be selected out of a range based on the specialcell category.

It would probably be possible to create a Case statement or some other structure to do more. However, there is a limit to this SpecialCell that could potentially get into the way.

You have us wondering about your objective. Unless your creating a really wonderful Add-In for a specific automation purpose, let us know about your objective.
Chances are that there is another way to accomplish the same objective.

 

[Brianwarnock]

I agree Rx_ much info about a cell can be obtained by the CELL or Type functions, which is why I made my earlier comment.

Brian