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:
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.