Find the index or column number of the last non zero cell in range

Asked Jan 21 '20 at 10:52

Active Jan 21 '20 at 11:53

Viewed 54 times

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:Q1 <>"")*COLUMN(A1:Q1)))

The range A1:Q1 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.

edited Jan 21 '20 at 11:53

braX

11,506
5
20
33

asked Jan 21 '20 at 10:52

OBU_Learner

2

There is a difference between blank or empty cells. Do you mean that your cells contain `""` as a result of formulas? – JvdV Jan 21 '20 at 11:09
1

Btw, your worksheetformula makes no sense =) It would always return 1 (in case any value <> ""). – JvdV Jan 21 '20 at 11:15
Sorry i wasnt clear, some cells contain a string, some are completely empty – OBU_Learner Jan 21 '20 at 11:16
Maybe `Application.Match(9.99E+307, Range(Cells(1, 1), Cells(1, 50)))` which will return column number? – shrivallabha.redij Jan 21 '20 at 11:16
*"some are completely empty"* Then you don't need to use any `WorksheetFunction`. See [this](https://stackoverflow.com/a/11169920/9758194) – JvdV Jan 21 '20 at 11:17
Also my worksheet formula should have read: =SUMPRODUCT(MAX((A1:Q1 <>"")*COLUMN(A1:Q1))), i have amended my original question – OBU_Learner Jan 21 '20 at 11:32
Thanks JvJD. That other thread was useful – OBU_Learner Jan 21 '20 at 11:37

Find the index or column number of the last non zero cell in range

0 Answers0