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:


YourRange.SpecialCells(TypeofSpecialCells).ActiontoPerform

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.


Sub SPCellsConstants() 'Excel VBA to copy the Constants in Col A
Range("A1", Range("A65536").End(xlUp)).SpecialCells(2).Copy
End Sub

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.


Sub SPCellsBlanks() 'Excel VBA to delete the Blank cells in Col A
On Error Resume Next 'Traps instance where there may be no blank cells
Range("A1", Range("A65536").End(xlUp)).SpecialCells(4).EntireRow.Delete
On Error GoTo 0
End Sub

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.

Sub FilteredData() 'Excel VBA to copy from one sheet to another sheet.
Range("A2:E" & Cells(Rows.Count, 1).End(xlUp).Row).Copy Sheet2.[a1]
End Sub

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.


Sub Copy1stVisibleCell() 'Excel VBA using SpecialCells
Range("A2", Range("A" & rows.count).End(xlUp)).SpecialCells(12).Cells(1, 1).Copy sheet2.[A1]
End Sub

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.

Sub CopyAllVisibleCell() 'Copies Visible Cells to Sheet2 A1 using Excel VBA
Range("A2", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(12).Copy sheet2.[A1]
End Sub

This method can be extended to a more specific group of Columns in the following VBA example from A to E.


Sub CopyRegionofVisibleCell() 'Excel VBA to copy from A to E.
Range("A2", Cells(Rows.Count, "E").End(xlUp)).SpecialCells(12).Copy sheet2.[A1]
End Sub

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.


Sub MoveVisible() 'Excel VBA to capture visible data.
Range("A1").CurrentRegion.Offset(1).SpecialCells(12).Copy Sheet2.[a7]
End Sub

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.

Sub MakeNeg()
[b1].Copy 'Excel VBA using a Negative (1)
Range("A2", Range("A65536").End(xlUp)).SpecialCells(12).PasteSpecial , xlMultiply
End Sub


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.


Sub MakeNeg2() 'Excel VBA using formula inside the VBA coding.
[A10:C20]=[if(A10:C20<>"",If(isnumber(A10:C20),A10:C20*A1,A10:C20),"")]
End Sub

As with most things the use of specialcells is best when practiced.