Would You Like Special Sauce with That

Are you familiar with the Go To Special functionality in Excel? This is another chunk of functionality that many Excel users either don't know exists or don't take advantage of. Check it out in Excel; select Edit ^ Go To and then click the Special button at the bottom left corner of the Go To dialog box. Figure 9.7 is the result.

Figure 9.7

Go To Special is another useful, yet underutilized, chunk of Excel functionality.

Go To Special

®

Select

O Comments

0 Rom differences

(*> ¡Constants!

0 Column differences

0 formulas

0 Erecedents

0 Members

O Dependents

» forectonty

0 Logicals

All levels

0 Errors

O Last cell

0 Blanks

O Visible cells onl^

0 Current tegion

0 Condtional formats

0 Current affray

O Data valdation

Octets

flJl

Same

1 OK t 1 5g 1

1 1

If you haven't used this yet, let me tell you—this handy little dialog box can be a real time-saver. Quick, what's the easiest way to select all of the text values in the range shown in Figure 9.8?

The answer? Press CTRL+G to display the Go To dialog box and then click the Special button. Choose the Constants option with only the Text checkbox checked and click OK. Figure 9.9 shows the results of this operation.

Most of the functionality served up by Go To Special can be accessed programmatically using the SpecialCells method.

YourSearchRange.SpecialCells(Type As XlCellType, [Value]) As Range

Figure 9.8

Ever needed to clear text values out of a range of otherwise numeric values?

Figure 9.8

Ever needed to clear text values out of a range of otherwise numeric values?

ET Microsoft Office EHCel 2003 Im I . 11 rr S : ÎM !:, : ■> M v

liiy Fils Edt

View Insert Format

Tools ' Window Heb

JC :

B s\m=rn • 'A :

Ü 1 V P

— i

[_J

. .Ï1 u -

! I I □ * I jä 1 ^Retiv changes...

A1

* fc a

, A I

B c

D

E

F

ex

1

I ? I

1 s

2

' ä 1

:

1 b

i

b t

s

1 c

6

o 1

7

1 d

a

d 1

-

3

1 e

10

e 1

11

ï r

12

13

v

II <

► w / Current Region \ Special Cells / 5hee I <

>J

Among other things, you can employ Go To Special to help you clean up data in lists.

ET Microsoft office EHcel 2003 Belct - cliâpter 9 Enaiopffcs.Kls

liiy |i Edt

View insert Format Tools Dai-: Wiodcw Heb

jjä

feP *

B S011 . a **

- À

-i I ""-S il I kil, X -.lilJl Rr|i!i -Jl-t : ,JÎ:

A1

S 5

A

B ! C 1 D 1 E

F

ex

1

I a I

1 —

2

1

a <

3

b

1 b

i

1

i I

S

B

1

c 1

7

mm

KM

a

i

d

-

a

■ UM 1

1 m

10

1

e i

11

i f

12

13

v

H i

> M X Current Region 1 Special Lells l i.hef i

>r

I Ready

The Type parameter is required and should be one of the xlCellType constants shown in Table 9.1.

Table 9.1: xlCellType Constants for Use with the SpecialCells Method

Constant xICeIITypeAIIFormatConditions xICeIITypeAIIVaIidation xICeIITypeBIanks xICeIITypeComments xICeIITypeConstants xICeIITypeFormuIas xlCellTypeLastCell xICeIITypeSameFormatConditions xICeIITypeSameVaIidation xICeIITypeVisibIe

Selects

Cells of any format Cells using data validation Empty cells

Any cell containing a comment

Cells with constant (or literal) values

Cells with formulas

The last cell in the used range

Cells having the same format

Cells having the same data validation criteria

All visible cells

If you chose either xlCellTypeConstants or xlCellTypeFormulas for the Type parameter, you can further define which cells to select using the optional Value parameter. By default, all constants or formulas are selected. Use one or more of the following constants: xlErrors, xlLogical, xlNumbers, or xlTextValues. For example, to duplicate the functionality demonstrated in Figure 9.8, you'd use something similar to this:

ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)

Note that you can specify more than one kind of value by adding constants together.

ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, _ xlErrors + xlTextValues)

One more thing, SpecialCells requires special care. If SpecialCells doesn't find any special cells, it generates a run-time error, so be sure to use error handling in any procedure that uses SpecialCells.

Listing 9.4 demonstrates one way to handle any run-time errors that SpecialCells may generate.

0 0

Post a comment