Special Cells – They’re special (at least that’s what Barney said)!

Special cells will make your interaction with Excel much more enjoyable, and they can be used both in worksheets and VBA, making them a wise investment.
1. From the worksheet:

The F5 key will take you to the Go To Box. There, the Special button awaits you. Once you press it, you’re just one step away from the magic:

Choose any of the options, press OK and two things can happen. If you’ve selected a range, the Special Cells will be selected within that range. If you’ve just selected one cell, the Special Cells on the whole worksheet will be selected.

The possibilities are endless. One example: if you have a table with row headers that you’d like to copy down into the empty rows below:

  • Select the range
  • F5 \ Special \ Blanks \ OK  (or the shortcut version F5 \ Alt+S \ k \ Enter
  • =B2 (or the cell right above your first selected blank
  • Ctrl + Enter
  • All the cells are now updated.
  • You can now select the whole column and do a copy \ paste values to get rid of the formulas.

 

There are of course many more uses for Special Cells, but this will get you going.

 

2. in VBA

You can access the Special Cells through the SpecialCells member of the Range Class, as in:

Range(“A1:L200″).SpecialCells(xlCellTypeBlanks).value=”Not Blank Anymore”

to fill in the blank cells of a range, or, much more useful, to copy only the visible cells of a range that’s been filtered (by the way, if you can avoid loops and use autofilter instead, your macros will thank you):

Sheets(“Sheet1”).Range(“A1:L200”).SpecialCells(xlCellTypeVisible).copy Sheets(“Sheet2”).cells(1,1)

 

You can play around with the Macro Recorder to get the different options, but here’s the list from the MSDN site:

  • XlCellType constants
  • xlCellTypeAllFormatConditions. Cells of any format
  • xlCellTypeAllValidation. Cells having validation criteria
  • xlCellTypeBlanks. Empty cells
  • xlCellTypeComments. Cells containing notes
  • xlCellTypeConstants. Cells containing constants
  • xlCellTypeFormulas. Cells containing formulas
  • xlCellTypeLastCell. The last cell in the used range
  • xlCellTypeSameFormatConditions. Cells having the same format
  • xlCellTypeSameValidation. Cells having the same validation criteria
  • xlCellTypeVisible. All visible cells
  • XlSpecialCellsValue constants:

– xlErrors
– xlLogical
– xlNumbers
– xlTextValues

 

Try it, you might like it.

Thomas

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s