loop to DYNAMIC last row

A loop is a sequence of instructions that will carry on until a certain condition is reached.

In order to implement VBA that will affect every cell in the column,

with a column that changes in size all the time,

we can set a dynamic lastrow variable.

This allows us to flow through columns effortlessly, resulting in powerful outcomes.

I will show you how to loop through a column

to it's last row and change every cell

that contains the word "Test" to blue fill.

Without further ado, here is the GIF:

Sub Looped()

'use this as a counter
Dim i As Long
'to make the range dynamic
Dim LastRow As Long

'assumes A is biggest column, hopefully it is primary keys
LastRow = Range("A" & Rows.Count).End(xlUp).Row

'start loop from first cell to bottom
For i = 1 To LastRow Step 1
'if current cell has a value of Test
If Cells(i, 1).Value = "Test" Then

'change color of cell
Cells(i, 1).Interior.ColorIndex = 5
End If
Next i
End Sub

Essentially the logic goes like this:

Find last row in column to set endpoint for loop.

Start loop at first cell in range.

If this cell meets a condition, do something.

Move to the next one.

If not move to the next one.

Repeat until last row.