VBA – Select Every 5th Row

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on February 22, 2023

Select Every 5th Row

Imagine that we have 20,000 rows of data in an Excel spreadsheet:

vba every other row

However, what about if you wanted to cut this data down such as take every 5th row ? This article shows how it can be done. The Row() function gives the number of each row:

vba select rows

The Mod Function allows us to perform modulo arithmetic:

=Mod(Number,Divisor)

Gives the remainder when “Number” is divided by “Divisor”. So for example:

=Mod(28,5)

Will give 3 the remainder when 28 is divided by 5.
So we can look at the row number and see what happens when we perform modulo arithmetic on it:

=IF(Mod(Row(),5)=0,1,0)

i.e consider the remainder when the row number is divided 5. If the remainder is 0 then put a 1 in the cell otherwise put a zero. For convenience I have hardcoded the row numbers in the above table . Now we insert additional column to work out when this happens. The formulae will be the modula formulae as above:

rows in vba

We see that rows that have row numbers that are divisible by 5 have modulo 1. If we now FILTER the data to show only the 1’s:

row

If we now select Visible Cells only and then copy this data only, we have our subset of our data.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

alt text

Learn More!

<<Return to VBA Examples

vba-free-addin

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples