last "used" col within selected range?

  • Thread starter Thread starter Keith R
  • Start date Start date
K

Keith R

I have a formula that currently uses embedded if statements to check each
column (CountA) and stop at the furthest column that has something in it.

Is there a more efficient way, without using VBA, to check any sized range
to return the last column that has something in it?

For example, if my range was E4:Z18, and I wanted to return a number
between 1 (if only column E has data) up to 22 (if column Z has data)

I have to assume there may be blank columns, or columns that are only
partially filled- I just need to locate the last one with something in
it...

TIA!
 
Keith,

Array enter, using Ctrl-Shift-Enter

=MAX((E4:Z18<>"")*COLUMN(E4:Z18))-(COLUMN(E4)-1)

or the non-array formula

=SUMPRODUCT(MAX((E4:Z18<>"")*COLUMN(E4:Z18)))-(COLUMN(E4)-1)

HTH,
Bernie
MS Excel MVP
 
Back
Top