(Solved) – Find the index or column number of the last non zero cell in range – VBA


I am trying to find the column number of the last non-zero cell in a range using VBA.

I have managed to do it in a worksheet using:

=SUMPRODUCT(MAX((A1:A50 <>"")*COLUMN(A1:A50)))

The range A1:A50 contains a mix of blank cells (“”) and cells with a string (“D1” etc)

However I cannot seem to transfer this into VBA.

I am trying:

Dim BrkUsed as Range
Dim LastUsed as Integer

Set BrkUsed = Range(Cells(1,1),Cells(1,50))

LastUsed = WorksheetFunction.SumProduct (WorksheetFunction.Max((BrkUsed <> "") * BrkUsed.Column))

I always get a type mismatch error which I assume is from the logic (BrkUsed <> “”) but I am unsure how to assign this correctly.

Leave a Reply

Your email address will not be published. Required fields are marked *