The (4:4<>0) creates a list of TRUE and FALSE values for every cell in row 4, TRUE if the cell <> zero and FALSE if the cell = zero.

These two arrays are multiplied together and the results is a list with a bunch of zeros and some numbers. The zeros are the results of a FALSE being multiplied with a column number. The numbers are the result of a TRUE being multiplied with a column number. This is because of the principle of coercion. Excel "coerces" TRUE's and FALSE's into 1's and 0's when performing arithmetic operations.

Lastly, the MAX function finds the largest value in the list of zeros and other numbers. The largest number is the column number that did not get killed with a FALSE. This number happens to be column containing the last non-zero value.

Now, as to why it won't work for you. I can't help with that. It works just fine for me both in VBA and as an array formula, see attached workbook. Maybe you have some corruption going on. Have you tried the code in a new sample workbook?

Kyle

Q-27375035-RevA.xlsm