[VBA] How to Get the First and Last Cells of a Table | Locating Positions with Range and UsedRange

When manipulating table data using Excel VBA, you often need to “automatically retrieve the first and last cells of a table.” For example, you might need to identify the starting position for data entry or add new data to the end.

This article explains how to get the first and last cells using both a fixed range and UsedRange, with specific code examples.

Getting First and Last Cells from a Fixed Range

Below is how to retrieve the first and last cells from an explicitly specified range, such as “B2:D4”.

Range("B2:D4").Cells(1).Select   ' First Cell (B2)
Range("B2:D4").Cells(9).Select   ' Last Cell (D4)

Explanation:

• Range("B2:D4") is a range of 3 rows x 3 columns, totaling 9 cells.
• .Cells(1) refers to the 1st cell in the range (Top-Left).
• .Cells(9) refers to the 9th cell in the range (Bottom-Right).
• The order of cells counts from left to right, then top to bottom.

Getting First and Last Cells from the Entire Sheet’s UsedRange

To get the first and last cells from the entire range actually used within the worksheet, use UsedRange.

With ActiveSheet.UsedRange
    MsgBox "First Cell: " & .Cells(1).Address & vbCrLf & _
           "Last Cell: " & .Cells(.Cells.Count).Address
End With

Explanation:

• .Cells(1) is the first cell (Top-Left) within the UsedRange.
• .Cells(.Cells.Count) is the last cell (Bottom-Right).
• .Address retrieves the address of each cell (e.g., $B$2) as a string.

When to Use This?

• First Cell: When you want to automatically determine the starting point for data processing.
• Last Cell: When you want to identify the next empty cell to add data.
• Loop Processing: Defining the start and end points clearly ensures safe looping through the range.

Summary

Correctly retrieving the first and last cells significantly improves the reliability of loops, data appending, and clearing processes.

When dealing with tables where the range changes dynamically, it is important to process them flexibly by combining these methods with UsedRange.