How to find the last cell with a specifc format

Question

Is there a way to find the last cell in a colum that contains a specific format?

Currently I use a for loop, below

LastRowNumber = Range("A:A").Find(What:="", after:=Range("A1"), searchdirection:=xlPrevious).Row

For i = 1 To 10000


            If Cells(LastRowNumber - i, 1).NumberFormat = "[h]:mm:ss" Then

                LastRowNumber = LastRowNumber - i

            End If


Next i

This loop find the last cell in the column then search the cell above to see if its in the format of [h]:mm:ss however this just loops through until it fails when i = LastRowNumber is there a better way to solve this?

https://superuser.com/questions/488132/excel-find-specific-number-format might help !! — sda11, Jan 30 '18 at 10:24

score 1 · Accepted Answer · answered Jan 30 '18 at 10:24

If you need the last cell containing the specific format, start to loop from the last used cell in the column:

For i = 10000 To 1 Step -1
    If Cells(i, 1).NumberFormat = "[h]:mm:ss" Then
        lastRowNumber = i
        Exit For
    End If
Next i

Make sure to make 10000 a variable, based on the last used cell in a given column, if this is applicable - Excel VBA- Finding the last column with data

How to find the last cell with a specifc format

1 Answers1