Special Cells Numbers

Sahil123

Well-known Member
Joined
Oct 31, 2011
Messages
673
Hi All,

I have tried recording a macro to give me formulas within a range with numbers and text etc and have tried several different combinations to see what the code is like but i get numbers.

Is there a list to show me what numbers are what and with a combination of text and numbers etc gives different number

i.e i got this

Sheets("Sheet1).Range("B1:B20").SpecialCells(xlCellTypeFormulas, 3).Select
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
If you look at the Object Browser in the VBEditor and use the search term "specialcells", one of the options will be the Enumerated class xlSpecialCellsValue which will list the constants xlErrors, xlNumbers, xlLogiclal and xlTextValues.
I find it easier to use named (descriptive) contestants than there value in coding

Code:
Sheets("Sheet1).Range("B1:B20").SpecialCells(xlCellTypeFormulas, xlNumbers).Select
 
Upvote 0
For numbers and textvalues,

Code:
Sheets("Sheet1).Range("B1:B20").SpecialCells(xlCellTypeFormulas, xlNumbers + xlTextValues).Select

Unfortunatly, one can't use this approach for Formulas and Constants, Union has to be used to combine cell types.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

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
Back
Top