SpecialCells() what is used for?

[lezawang]

Hi I am trying to understand the code below which highlight blank cells. 2 parts I did not understand

1) what does this line do " Set Dataset = Selection "?

2) what SpecialCells() function does here --> Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed

Thank you so much.

source: https://trumpexcel.com/excel-macro-examples/
'This code will highlight all the blank cells in the dataset
Sub HighlightBlankCells()
[FONT=&quot][/FONT][FONT=&quot][/FONT]Dim Dataset as Range
Set Dataset = Selection
Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed
End Sub

 

[mikerickson]

1) Dataset is a variable. the line

Set Dataset = Selection

sets the value of the variable, Dataset, to those cells that are currently selected.

2)The line

Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed

takes those cells in Dataset that are blank* and makes them red.

*- one should be cautious about "blank" cells. Cells that contain formulas that evaluate to "" are considered "not blank".

 

[Joe4]

1) what does this line do " Set Dataset = Selection "?

It just sets the "Dataset" range to be whatever cells are currently selected when the macro starts (so it will run on whatever range you have selected).

2) what SpecialCells() function does here --> Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed

Special cells are a set of special cell commands. This is simply selecting all blank cells in your selected range and making their color red.
You can see the Special Cells options if you hit F5 while on the sheet, and click the "Special" button.

 

[lezawang]

Thank you all so much for helping out to understand the code. I understood.
I just want to know, if that code can be modified to "undo" the highlight.
I can clear the selection but that is not "undo". is it doable?
thanks a lot once again.

 

[Rick Rothstein]

Just to follow up, the SpecialCells function is the VBA equivalent of the Go To dialog's Special button. Press F5 (or CTRL+G) to bring up the Go To dialog box and then click the button labeled "Special"... most of the possible options you see are available (some are not) via the SpecialCells function in your code.

 

[shg]

There is no undo in VBA. You could apply a different color, or no fill, but any prior fill is permanently gone unless you write a lot more code.