Finding last used column

[ken]

G'day there One & All,

I've got a worksheet with a rectangular, contiguous range. The range
has dates as headings across the top, with offices down the left hand
column.

Once a week, each office posts their data and it's collected by my
workbook to be stored on the sheet according to date & office.

I'm trying to write a reporting sheet and found that I need to
determine the date when last reports were made. The data may include
blanks, so my idea of using dynamic ranges fell over. Also there is
other stuff to the right of my range, so using .end(xltoright) won't
work either.

The chances of all offices having zero entries is so low as to be
discounted, so what I want to do is to find which is the latest date
entered by any office. Having found the rightmost used column, I can
determine the date but I've had no success finding that column.

I've tried a few unsuccessful ideas of my own, and also tried to adapt
an Array Formula I found on Chip Pearson's site:

=INDIRECT(ADDRESS(MAX((B5:AA5<>"")*ROW(B5:AA5)),COLUMN(B5:AA5),4))

I thought that if I had one of these at the end of each row, and then
found the max value I might get the column number for my date. However I
only got '0' as a result, so I must have gotten something wrong.

If anyone has any ideas as to how I can work this out please come
forth. I'll be most grateful. BTW, I don't mind if I need a UDF in code.
There's plenty of code already, one more function won't hurt

Thanks for listening,
Ken

 

[Sean Timmons]

Completely ripped off from Gord Dibben...


=LOOKUP(99^99,A:A) will return last number in column A

=LOOKUP(2,1/(A1:A65535<>""),A1:A65535) will return anything.

 

[pshepard]

Hi Ken,

This works for me, then do a MAX of the column that these are in.
=INDIRECT(ADDRESS(1,MATCH(99+E9999,B5:AA5,1)))

 

[ken]

G'day there Sean,

Completely ripped off from Gord Dibben...

Well thank you very much to you both

=LOOKUP(99^99,A:A) will return last number in column A

That gave me a working start. I mucked about with LOOKUP & MATCH until
I realised that there wasn't a function to do what I wanted so I wrote
one myself. Having said yesterday that ".END(xltoright)" wouldn't work,
I had a sudden brain wave and used:

Function lastColumn(rng As Range)

lastColumn = rng.End(xlToLeft).Column

End Function

with =lastColumn(rng) at the end of each row in my range. "rng" is
the address for the last cell in each row. It's then simple to use the
MAX function on that column to get the result I need.

It was quite simple in the end, which suits my abilities well, but I'd
not have got there if you hadn't given me a formula that gave me an
answer in the ballpark of what I wanted. Thanks for that. It's much
appreciated.

See ya
Ken

 

[ken]

G'day there Peggy,

Hi Ken,

Howdy

This works for me, then do a MAX of the column that these are in.
=INDIRECT(ADDRESS(1,MATCH(99+E9999,B5:AA5,1)))

I find that I'm getting weird results from that formula. For instance,
the ranage B5:AA5 is the first row of my data range, but the formula
gives me a result from 4 rows above. I have not idea how. I'm still
playing with it though, as for some reason I can't get my UDF to auto
update. Something's obviously wrong and I may hit my 'pooter with a
hammer in a minute or two.

I wonder if the problem arises because I'm using the MATCH function
on unsorted data?

Thanks for your input. I really appreciate it.

Still working on it,
See ya,
Ken

 

[Don Guillett]

If your desire is to find the last column anywhere on the worksheet. This
will save you a lot of time. Try this ONE liner, as written.
lc = Cells.Find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious).Column



'msgbox lc

 

[pshepard]

Hi Ken,

=INDIRECT(ADDRESS(1,MATCH(99+E9999,B5:AA5,1)+1))

This corrects the formula I posted last time - I forgot to take into account
that the formula is starting on column B, so +1 needed to be added in order
to get it as if it started looking in column A.

 

[ken]

G'day there Peggy,

=INDIRECT(ADDRESS(1,MATCH(99+E9999,B5:AA5,1)+1))

This corrects the formula I posted last time - I forgot to take into account
that the formula is starting on column B, so +1 needed to be added in order
to get it as if it started looking in column A.

Thanks for that.

Of course, *I'm* perfect and have *never* left such an offset out of my
formulas!! <chucklechucklechortle> (If you believe that I have a nice
used car you may be interested in).

Thanks for your help,
Ken