Category Archives: Excel Training

Remove Duplicates in Excel

How to remove duplicates from a column of data in Excel

It’s not uncommon when adding or importing records that you may need to remove duplicates in Excel. Whilst filtering and deleting records to make a list of unique items is an effective solution, there is an even easier way.
Simply follow these steps to remove duplicates in Excel.

Step 1 Copy the column of data containing the repeated or duplicate items to a blank column.

Step 2 Highlight the copied column of data and select Data, Remove Duplicates.

RemoveDuplicates

Step 3 From dialog box remove the tick next to ‘My data has headers’ if you did not include a header at the top of your data.

RemoveDuplicatesDialog

Remove Duplicates in Excel

 

Step 4 Press OK and all the duplicates will be removed.

A message displays at the end telling you how many duplicates have been removed.

RemoveDuplicatesResult2

  Continue reading

SpecialCells in VBA

How to use SpecialCells method in Excel VBA

SpecialCells in VBA is a really useful method to deploy in Excel. It returns a Range Object that only covers the type of cells you specify. You can use the SpecialCells in VBA Method to return a Range Object that only holds numbers, text, blank cells, formulae, cells with datavalidation, cells with conditional formatting, the last cell in the worksheet, cells with comments and all visible cells.

If you for example want to change formatting for all numbers in a worksheet you do not need more than one line in the Visual Basic Editor to do it.

Cells.SpecialCells(xlCellTypeConstants, xlNumbers).Style = “currency”

This line will change all numbers in the active worksheet to currency format.  The Range object Cells is used to tell Excel that you want to look at all the cells and the special cells method to decrease it to in this example only constants (xlCellTypeConstants) and again to decrease it to only numbers the criteria  xlNumbers is added to the SpecialCells Method.

Similar we can use the SpecialCells Method to return a Range Object that only holds text.

Cells.SpecialCells(xlCellTypeConstants, xlTextValues).Font.ColorIndex=3

This VBA line will change the font colour to red for all text in the active worksheet.

The SpecialCells Method syntax is;
expression.SpecialCells(Type, Value)

The Expression have to be a Range object such as Cells, Range(“A1:B200″), ActiveSheet.UsedRange etc.

The different types of special cells are:

  1. xlCellTypeAllFormatConditions (all formatted cells)
  2. xlCellTypeAllValidation (all cells with datavalidation)
  3. xlCellTypeBlanks (all blank cells)
  4. xlCellTypeComments (all cells with notes)
  5. xlCellTypeConstants (all cells containing constants (numbers or text))
  6. xlCellTypeFormulas (all cells with formulas)
  7. xlCellTypeLastCell (The last cell in all used ranges)
  8. xlCellTypeSameFormatConditions (all cells with the same formatting also conditional formatting)
  9. xlCellTypeSameValidation (all  cells with the same datavalidation)
  10. xlCellTypeVisible (alll visible cells)

You can also use a combination of the above options.

Cells.SpecialCells(xlCellTypeConstants, xlNumbers).SpecialCells(xlCellTypeAllValidation).Font.Color = vbRed

This line of VBA code will add red font colour to all cells with numbers & Datavalidation.

The SpecialCells in VBA Method is also very powerful if you want to test your data in an If Then Else decision code.

Capture

SpecialCells in VBA

 

In the example above all numbers are tested in the active worksheet if the value is greater than 7500. If the test is true 10% is added. The For Each loop is only running through cells with numbers.

The SpecialCells in VBA Method can be very handy if you need to remove blank rows from you Excel lists or Excel databases.

Capture

and after running the macro

Capture2

In the above example The SpecialCells Method finds all blank cells in the range from A3 to A27 and deletes the entire row.

You have a lot of variations you can use and you will find out that when you start using The SpecialCells method  you will save a lot of lines in your macros!