Using MOD Operator in VBA

In Excel, MOD is a function, but in VBA, it is an arithmetic operator that you use to get the remainder when you divide a number by another.

In this article, I’ll tell you everything you need to know about the MOD operator in VBA and show you some practical examples where it can be useful.

Table of Contents

MOD Syntax in VBA

result = number1 Mod number2

number1 – This is the dividend, the number you are dividing.
number2 – This is the divisor, the number you are dividing by.
result – This is the remainder after the division.

You can use the Mod operator in VBA code to, for instance, display the result in a cell, highlight every alternate row in a dataset, or delete every third row in a dataset.

Let’s see some practical examples with the Mod operator in action.

Example #1: Display the Result in a Cell

Let’s start with a simple example where I want to divide the number 50 by 4 and display the remainder in cell A1.

Here’s how you can do it:

Copy the code below into a VBA standard module.

Sub VBA_MOD()
Range("A1") = 50 Mod 4
End Sub

Place the cursor inside the code and press F5 to run the code.

You will see the remainder (2) displayed in cell A1.

Using MOD operator to get a result in cell

Working With Decimals

The Mod operator works only with whole numbers. If you supply decimal values, VBA first rounds them to the nearest whole number before performing the calculation

Using MOD operator with decimals in VBA.

Working With Negative Values

When you use negative values with the Mod operator, the remainder takes the sign of the dividend (the first number), as shown in the examples below.

Using mod with negative values in VBA Excel.

Example #2: Highlight Alternate Rows in a Dataset

Suppose you have the dataset below on Sheet1 of your workbook.

Dataset to highlight alternate rows in Excel.

To enhance the readability of the dataset, you can use the Mod operator in VBA code to highlight every alternate row in light blue.

Here’s how you can do it:

Copy the code below to a standard VBA module.

Sub HighlightAlternateRows()
Dim ws As Worksheet
Dim lastRow As Long
Dim lastCol As Long
Dim i As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
For i = 2 To lastRow
If i Mod 2 = 0 Then
ws.Range(ws.Cells(i, 1), _
ws.Cells(i, lastCol)).Interior.Color = RGB(220, 230, 241)
Else
ws.Range(ws.Cells(i, 1), _
ws.Cells(i, lastCol)).Interior.Color = xlNone
  	End If
 Next i
End Sub

Place the cursor inside the code and press F5 to execute it.

The code highlights every other row in the dataset in blue.

Alternate rows highlighted in a data set using the MOD operator in VBA.

How the Code Works

lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row – this ensures the loop starts at the last filled row.
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column – this finds the last column with data in row 1.
i Mod 2 – if the remainder is zero, the row number is even and gets the light blue background color. If the remainder is 1, the row number is odd and gets no fill color.

You can adapt the code to your needs by adjusting the sheet name, the last row and column values, as well as the highlight color.

Example #3: Delete Every Third Row

Suppose you have the dataset below on Sheet1 of your workbook.

The dataset has introductory notes in every third row from the bottom.

To clean the dataset, use the Mod operator in VBA to delete every third row starting from the bottom, leaving only rows that contain employee data.

Here’s how you can do it:

Copy the code below to a standard VBA module.

Sub DeleteEveryThirdRowFromBottom()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim counter As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    counter = 0
    For i = lastRow To 2 Step -1
        counter = counter + 1
        If counter Mod 3 = 0 Then
ws.Rows(i).Delete
        End If
    Next i
End Sub

Place the cursor inside the code and press F5 to run it.

The code deletes every third row in the dataset, starting from the bottom.

Dataset where every third row has been deleted using mod in vba

How the Code Works

It starts from the last row of the dataset and moves upwards.
It keeps a counter to track how many rows have been processed.
It deletes the row if the counter is a multiple of 3.
It stops at row 2 to preserve your header.

You can adapt the code to your needs by changing the target sheet name.

I have shown you how to use the Mod operator in VBA. I hope you found the tutorial helpful.

Other Excel VBA articles you may also like: