How to Find the Last Row with Numerical Values in Excel VBA

Learn how to effectively find the last row containing any numerical value in a range using Excel VBA, avoiding issues with text values.
---
This video is based on the question https://stackoverflow.com/q/79382917/ asked by the user 'samysnes' ( https://stackoverflow.com/u/29340256/ ) and on the answer https://stackoverflow.com/a/79385173/ provided by the user 'Scott Craner' ( https://stackoverflow.com/u/4851590/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Excel VBA - How to find last row of range that contains any numerical value (not text)

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to drop me a comment under this video.
---
The Problem: Finding Last Row with Numbers Only

In Excel VBA, determining the last row that contains any data is common, but what if you only want the last row that contains numerical values, excluding text?

Using Range.Find with a wildcard (e.g., "*") returns any content, including text, which is not ideal if your range mixes numbers and text.

Why Range.Find Isn't Enough

[[See Video to Reveal this Text or Code Snippet]]

This finds the last non-empty cell but can't distinguish between numbers or text.

A Reliable VBA Approach: Using Application.Match

A better way is to leverage Application.Match with a very large number to find the last cell with a numerical value.

How it works

Application.Match searches for the largest number less than or equal to the lookup value.

By providing a number larger than any expected value, it returns the position of the last numeric entry.

Sample Code

[[See Video to Reveal this Text or Code Snippet]]

Notes

Replace 99999999999999999 with a number greater than your maximum expected numerical value.

If your data range is smaller, adjust Range("A:A") accordingly.

Summary

Range.Find with wildcards returns any content (text or numbers), not just numbers.

Using Application.Match with a large number returns the position of the last numeric cell efficiently.

This method is both simple and reliable for mixed-content ranges.



By applying this method, you can precisely detect the last row containing a numeric value, which is crucial for data processing tasks that differentiate numbers from text.