excelvba

Excel VBA - How to find last row of range that contains any numerical value (not text)

I've been struggling to find a way to find the last row of a range that contains any numerical value (not text). I have a range in which some cells contain text and some cells contain whole numbers.

Right now I'm using Range.Find as follows:

LastRow = ActiveSheet.Range("A6:A167").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

The issue is using the asterisk wildcard will return a result on ANY value including text. Is there a way to search for a numerical wildcard to return the last row containing a number?

Thanks!

Solution

  • You can use Application.Match with a large number:

    LastRow = Application.Match(99999999999999999,ActiveSheet.Range("A:A"),1)

    I would make the 99999999999999999 at least one digit more than your largest expected number, just in case.