Macro 57: Hide All Rows but Those Containing Duplicate Data
With the previous macro, you can quickly find and highlight duplicates in your data. This in itself can be
quite useful. But if you have many records in your range, you may want to take the extra step of hiding all
the non-duplicate rows. Doing so exposes the duplicate values further because they will be the only rows
showing.
How it works
This macro enumerates through the cells in the target range, leveraging the For Each statement to
activate each cell one at a time. We then use the CountIf function to count the number of times
the value in the active cell occurs in the range selected. If that number is one, we hide the row in
which the active cell resides. If that number is greater than one, we format the cell yellow and leave
the row visible.
Sub Macro57()
‘Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
‘Step 2: Define the target Range.
Set MyRange = Selection
‘Step 3: Start looping through the range.
For Each MyCell In MyRange
‘Step 4: Ensure the cell has Text formatting.
If Not IsEmpty(MyCell) Then
If WorksheetFunction.CountIf(MyRange, MyCell) > 1 Then
MyCell.Interior.ColorIndex = 36
MyCell.EntireRow.Hidden = False
Else
MyCell.EntireRow.Hidden = True
End If
End If
‘Step 5: Get the next cell in the range
Next MyCell
End Sub
1. Step 1 declares two Range object variables, one called MyRange to hold the entire target
range, and the other called MyCell to hold each cell in the range as we enumerate through
them one by one.
2. Step 2 fills the MyRange variable with the target range. In this example, we are using the
selected range — the range that was selected on the spreadsheet. You can easily set the
MyRange variable to a specific range such as Range(“A1:Z100”). Also, if your target
range is a named range, you can simply enter its name: Range(“MyNamedRange”).
3. Step 3 loops through each cell in the target range, activating each cell as we go through.
4. We first use the IsEmpty function to make sure the cell is not empty. We do this so the
macro won’t automatically hide rows with no data in the target range.
We then use the WorksheetFunction object to run a CountIf function in VBA. In this
particular scenario, we are counting the number of times the active cell value (MyCell.
Value) is found in the given range (MyRange).
If the CountIf expression evaluates to greater than 1, we change the interior color of the cell
and set the EntireRow property to Hidden=False. This ensures the row is visible.
If the CountIf expression does not evaluate to greater than 1, the macro jumps to the Else
argument. Here we set the EntireRow property to Hidden=True. This ensures the row is
not visible.
5. Step 5 loops back to get the next cell. After all cells in the target range are activated, the
macro ends.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11 on your keyboard.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
