VBA: Selecting Every 5th Row
Imagine that we have 20,000 rows of data in an Excel spreadsheet:
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:
The mod function allows us to perform modulo arithmetic:
Gives the remainder when “Number” is divided by “Divisor”. So for example:
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:
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:
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:
If we now select Visible Cells only and then copy this data only, we have our subset of our data.