[Excel VBA] How to Search Cells from Bottom to Top (Using Step -1)

2025年12月26日

Introduction

When working with Excel VBA, you often encounter situations where you need to search for cell contents from the bottom up. For example, you might want to find the very last occurrence of a specific name, like “Mori,” in a long list in Column A.

In this article, I will explain how to search cells in reverse order using the For Next loop combined with Step -1.

What We Want to Achieve

Search for the text “Mori” in Column A, starting from the bottom and moving up.
Activate the cell when found and immediately stop the process.

VBA Code

Below is the code example used to achieve this.

Dim c1 As Long
Dim i As Long

c1 = Range("A2").Column

' Loop from the last used row in Column A down to row 1
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
    If Cells(i, c1).Value = "Mori" Then
        Cells(i, 1).Activate
        Exit For
    End If
Next i

Code Explanation

Lines 1-2: Variable Declaration

Dim c1 As Long
Dim i As Long

c1 is a variable to store the column number (Column A in this case).
i is a variable used to count the row numbers.

Line 3: Get Column Number for Column A

c1 = Range("A2").Column

We retrieve the column number of cell A2. Since this is Column A, c1 becomes 1.

Line 4: The For Loop (Bottom to Top)

For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1

Cells(Rows.Count, 1).End(xlUp).Row: This gets the row number of the last used cell in Column A.
To 1 Step -1: This tells the loop to count down by 1 until it reaches row 1. This creates the reverse search effect.

Lines 5-7: Condition and Action

If Cells(i, c1).Value = "Mori" Then
    Cells(i, 1).Activate
    Exit For
End If

If the value of the cell is “Mori,” the code activates that cell and uses Exit For to stop the loop immediately.

Line 8: Continue Loop

Next i

The loop continues moving up one row at a time until the condition is met or it reaches the first row.

Tips for Application

Make it dynamic: You can replace “Mori” with a variable to allow for flexible searching.
Change the action: Instead of activating the cell, you can highlight it (change the background color) or write a note in a different column.
Change the column: If you want to search Column B instead, simply change the code to c1 = Range("B1").Column.

Summary

By using Step -1 in a For Next loop, you can easily write code that searches cells from the bottom to the top.

In this example, we searched for the text “Mori” in Column A, but this method can be applied to any column or search term. Leveraging this flexible loop processing in VBA will make your Excel automation even more convenient.