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):
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:
Try it, you might like it.