5 Handy VBA Macros: Mod, Second Highest, Sum by Colour and Delete Blank Rows

📎 This article includes 1 downloadable practice file ↓

Some macros earn their place by being small and reusable. These five come up in almost every office I’ve worked in. Each one shows a technique you’ll keep using: the Mod operator, worksheet functions in VBA, reading cell colours, deleting rows safely, and built-in methods that save writing loops.

1. Every Nth row with Mod

Mod gives the remainder after division: 7 Mod 3 = 1. A remainder of zero means “divisible”, which makes it perfect for every 2nd, 5th or 10th row.

Sub HighlightEveryFifthRow()
    Dim ws As Worksheet, r As Long
    Set ws = ActiveSheet
    For r = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        If (r - 1) Mod 5 = 0 Then ws.Rows(r).Interior.Color = RGB(255, 247, 214)
    Next r
End Sub

The same idea inserts a subtotal every 10 rows, or splits a long list into pages.

2. Second-highest value (and its name)

Sub SecondHighestSale()
    Dim rng As Range, v As Double, pos As Variant
    Set rng = ActiveSheet.Range("E2:E500")
    v = Application.WorksheetFunction.Large(rng, 2)
    pos = Application.Match(v, rng, 0)
    MsgBox "Second highest: " & Format(v, "#,##0") & " by " & rng.Cells(pos, 1).Offset(0, -4).Value
End Sub

Application.WorksheetFunction gives VBA access to most Excel functions — LARGE, SUMIFS, COUNTIF, VLOOKUP. Using Application.Match (without WorksheetFunction) returns an error value instead of crashing when nothing is found.

3. Sum by fill colour

Excel has no built-in function for this, so people colour cells and then add them up by hand. A small function fixes that:

Public Function SUMBYCOLOR(rng As Range, colourCell As Range) As Double
    Dim c As Range
    For Each c In rng
        If c.Interior.Color = colourCell.Interior.Color And IsNumeric(c.Value) Then
            SUMBYCOLOR = SUMBYCOLOR + c.Value
        End If
    Next c
End Function

Use it in a cell: =SUMBYCOLOR(E2:E200, H1) where H1 has the colour you want.

4. Delete blank rows safely

Sub DeleteBlankRows()
    Dim ws As Worksheet, r As Long
    Set ws = ActiveSheet
    Application.ScreenUpdating = False
    For r = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row To 2 Step -1     ' bottom up!
        If Application.WorksheetFunction.CountA(ws.Rows(r)) = 0 Then ws.Rows(r).Delete
    Next r
    Application.ScreenUpdating = True
End Sub

Loop from the bottom up. Going top-down, deleting row 5 moves row 6 into position 5 and the loop skips it. Checking the whole row with COUNTA avoids deleting rows that are blank only in column A.

5. Remove duplicates in one line

ActiveSheet.Range("A1").CurrentRegion.RemoveDuplicates Columns:=Array(1, 3), Header:=xlYes

Removes rows where columns 1 and 3 together repeat. Always run it on a copy first — there is no undo after a macro.

Keep handy macros everywhere: Personal Macro Workbook

1. Developer › Record Macro, choose Store macro in: Personal Macro Workbook, record anything and stop.
2. Excel creates PERSONAL.XLSB, which opens hidden with Excel every time.
3. Paste these macros into its module in the VBA editor. They’re now available in every workbook via Alt+F8, and you can add them to the Quick Access Toolbar.

Where people go wrong

• Deleting top-down skips rows — always Step -1.
• WorksheetFunction errors crash the macro when there’s no match; use Application.Match and test IsError.
• Running destructive macros on real data — keep a copy, or call a backup Sub first (ThisWorkbook.SaveCopyAs).

Practice

The practice workbook includes all five macros on buttons and sample data with blank rows, coloured cells and duplicates to clean.

Next: loops — For, For Each, Do While and Do Until.