Using SpecialCells in Excel & VBA
Using SpecialCells in Excel VBA is a way for you to isolate like data very quickly. Specialcells in Excel can be more efficient from a VBA coding perspective than using filters and it most certainly more efficient than looping. The most common items I use in the SpecialCells arsenal are Blanks, Constants and Visiblecells. In Excel if you press F5 the following is what you will see the following dialog:
Click on Special and the following dialog will appear. These are the most common methods I use (in Red).
In Excel VBA the above are the inbuilt objects you can call on. The ones in red are the ones I use most frequently. This is not to detract from the others, I just find myself going back to the same functions time and again.
The construct in Excel VBA for SpecialCells is as follows:
Once you have the syntax it is just a matter of applying your particular problem to the code.
Using SpecialCells With VBA
The following Excel VBA code will copy all of the constants in Column A with the help of the specialcells method.
The following Excel VBA will delete all of the blanks in Column A. It has an error trap in case there are no blanks in the chosen column. This will prevent the code from breaking if it cannot find blank cells.
On Error Resume Next 'Traps instance where there may be no blank cells
Capture Visible Cells
Excel Workbooks don’t always show all the information on the sheet you are looking at. While hiding rows is not really advisable, in Excel occasionally you come across cells which are hidden. It is better to group data using the group command to capture only visible information.
In the Autofilter with VBA article I use coding like the following to capture all of the cells which have been filtered. This works well in a Filtered list as only the visible cells will be copied.
However, the above method will not work effectively when data is grouped or hidden. We can use the Special Cells command to isolate visible cells. In the following example we will go through three methods. The first of these is to capture the first visible cell of data in Column A.
The second and more practical step is to capture all of the data in a particular Column where the data is visible. I have excluded the headings by starting the range in Row 2.
This method can be extended to a more specific group of Columns in the following VBA example from A to E.
Probably the most practical method is to capture all of the data in the current region which is visible and copy it to sheet 2. This method will exclude the headings.
Turn all of the values in a Column Negative
Turning values in a range from positive to negative can be done with vba quite easily. Put some numbers in Col A.
Now in B1 put -1. Run the following VBA code over the file.
Range("A2", Range("A65536").End(xlUp)).SpecialCells(12).PasteSpecial , xlMultiply
The xlMultiply will multiply all the values in a column or file by -1, making the data negative in one movement. I often do the same thing to multiply by 1000. It is a neat trick in Excel and one worth knowing about.
Put 1000 in Cell B1 and run the code above. It will multiply everything by 1000.
This is not using specialcells but is quite a smart way to update a static Excel range with VBA. Put -1 in A1 and some numbers between A10 and C20.
As with most things the use of specialcells is best when practiced.