Duplicates (Highlight duplicates in Bold Red)

There are times you need to highlight duplicate data in your worksheet. This macro does the trick.

Sub DupsRed()
Application.ScreenUpdating = False
Rng = Selection.Rows.Count
For i = Rng To 1 Step -1
myCheck = ActiveCell
ActiveCell.Offset(1, 0).Select
For j = 1 To i
If ActiveCell = myCheck Then
Selection.Font.Bold = True
Selection.Font.ColorIndex = 3
End If
ActiveCell.Offset(1, 0).Select
Next j
ActiveCell.Offset(-i, 0).Select
Next i
Application.ScreenUpdating = True
End Sub

Deleting Empty Rows

To delete empty rows in a selected range we can use the following macro. The macro here uses the For Next Loop. First the macro counts the rows in a selected range to determine the when the macro should stop. The For Next statement acts as a counter.

Sub DelEmptyRow()
Rng = Selection.Rows.Count
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = False
For i = 1 To Rng
If ActiveCell.Value = "" Then 'You can replace "" with 0 to delete rows with the value zero
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next i
Application.ScreenUpdating = True
End Sub

The statement "Application.ScreenUpdating = False" prevents the screen from updating to ensure the macro runs faster and the screen will not flicker. Don't forget to set it back to "True".

Current Cell Content

Sometimes we need to know what the cell contains ie dates, text or formulas before taking a course of action. In this example a message box is displayed. Replace this with a macro should you require another course of action.

Sub ContentChk()
If Application.IsText(ActiveCell) = True Then
MsgBox ("Text") 'replace this line with your macro
Else
If ActiveCell = "" Then
MsgBox ("Blank cell") 'replace this line with your macro
Else
End If
If ActiveCell.HasFormula Then
MsgBox ("formula") 'replace this line with your macro
Else
End If
If IsDate(ActiveCell.Value) = True Then
MsgBox ("date") 'replace this line with your macro
Else
End If
End If
End Sub

Current Date

It's a good idea to insert the current date when you save the file so that you can tell if it's the latest version. Of course this is shown under file properties but how many people know where to find it? You could also put the current date in the footer of your print out. It is ideal if the date does not change unless the file is saved. You can use this code. (On the drop down list that says declaration, select before save and you will see the 1st line of code shown below - more details refer to Auto Run macro)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("A1") = Now 'Select any cell you want
End Sub

Counter

To use a counter in your macro, just assign any cell to retain the value. In this example the cell A1 is chosen. Each time the macro is run, it adds the value 1 to the cell A1.

Sub Count()
mycount = Range("a1") + 1
Range("a1") = mycount
End Sub

Copying A Range

Copy data from a specific range can be done with this macro. Here data is copied from the current sheet to the activecell. (Refer to Active Cell)

Sub CopyRange()
Range("A1:A3").Copy Destination:=ActiveCell
End Sub

To copy from a range in another sheet (eg Sheet3) to the active cell you need to change the code to;

Sheets("sheet3").Range("A1:A3").Copy Destination:=ActiveCell

Counting Rows & Columns & Sheets

When you have selected a range, it is sometimes useful to know how many rows or columns you have selected as this information can be used in your macros (for eg when you have reached the end, you will know it is time to stop the macros. This macro will do the trick.

Sub myCount()
mycount = Selection.Rows.Count 'Change Rows to Columns to count columns
MsgBox (mycount)
End Sub

The next macro counts the number of sheets instead. Refer to Protecting all sheets macro which uses this method.

Sub myCount2()
mycount = Application.Sheets.Count
MsgBox (mycount)
End Sub

Active Cell

An active cell is the current cell that is selected. This term is used in many macros. This can be used as a marker. A good example is when you need to move from your current cell. Refer to Moving your cursor macro.

Sheets("sheet3").Range("A1:A3").Copy Destination:=ActiveCell