Hello!

I have data in a single row array. The data is not contiguous such that
there are holes in the data. Several cells may be blank in the first few
cells, in the middle cells, or in the last cells. I need to identify which
cell is the one where the data begins. In addition, I need to identify which
cell is the one where the data ends. It is not necessary to locate "holes"
in the data set. I would like to use functions only to identify these first
and last cells.

Thank you very much!!

Tom

Skyscan,

Array enter each of these (enter using Ctrl-Shift-Enter)

First filled cell of row 2:
=ADDRESS(ROW(2:2),MIN(IF(2:2<"",COLUMN(2:2),256)) )

Last filled cell of row 2:
=ADDRESS(ROW(2:2),MAX(IF(2:2<"",COLUMN(2:2),0)))

HTH,
Bernie
MS Excel MVP


"Skyscan" wrote in message
...
Hello!

I have data in a single row array. The data is not contiguous such that
there are holes in the data. Several cells may be blank in the first few
cells, in the middle cells, or in the last cells. I need to identify which
cell is the one where the data begins. In addition, I need to identify which
cell is the one where the data ends. It is not necessary to locate "holes"
in the data set. I would like to use functions only to identify these first
and last cells.

Thank you very much!!

Tom

Thank you very much!!

Tom

"Bernie Deitrick" wrote:

Skyscan,

Array enter each of these (enter using Ctrl-Shift-Enter)

First filled cell of row 2:
=ADDRESS(ROW(2:2),MIN(IF(2:2<"",COLUMN(2:2),256)) )

Last filled cell of row 2:
=ADDRESS(ROW(2:2),MAX(IF(2:2<"",COLUMN(2:2),0)))

HTH,
Bernie
MS Excel MVP


"Skyscan" wrote in message
...
Hello!

I have data in a single row array. The data is not contiguous such that
there are holes in the data. Several cells may be blank in the first few
cells, in the middle cells, or in the last cells. I need to identify which
cell is the one where the data begins. In addition, I need to identify which
cell is the one where the data ends. It is not necessary to locate "holes"
in the data set. I would like to use functions only to identify these first
and last cells.

Thank you very much!!

Tom

Sorry, but I was not able to get it to work. Please explain the comment
about array entering using Cntrl-Shift-Enter.

Thanks!

Tom

"Skyscan" wrote:

Thank you very much!!

Tom

"Bernie Deitrick" wrote:

Skyscan,

Array enter each of these (enter using Ctrl-Shift-Enter)

First filled cell of row 2:
=ADDRESS(ROW(2:2),MIN(IF(2:2<"",COLUMN(2:2),256)) )

Last filled cell of row 2:
=ADDRESS(ROW(2:2),MAX(IF(2:2<"",COLUMN(2:2),0)))

HTH,
Bernie
MS Excel MVP


"Skyscan" wrote in message
...
Hello!

I have data in a single row array. The data is not contiguous such that
there are holes in the data. Several cells may be blank in the first few
cells, in the middle cells, or in the last cells. I need to identify which
cell is the one where the data begins. In addition, I need to identify which
cell is the one where the data ends. It is not necessary to locate "holes"
in the data set. I would like to use functions only to identify these first
and last cells.

Thank you very much!!

Tom

Tom,

Instead of typing in the formula and pressing <Enter, you need to type the
formula in, hold down the <Ctrl key, hold down the <Shift key, and while
those two keys are held down, press the <Enter key. This enters the
formula as an array formula, which is a special formula type that evaluates
each cell in the range on a cell by cell basis, rather than all at once.

If you cannot get the formulas to work, I will send you a sample workbook
with the formulas working, if you post your email address or contact me
privately.

HTH,
Bernie
MS Excel MVP


"Skyscan" wrote in message
...
Sorry, but I was not able to get it to work. Please explain the comment
about array entering using Cntrl-Shift-Enter.

Thanks!

Tom

"Skyscan" wrote:

Thank you very much!!

Tom

"Bernie Deitrick" wrote:

Skyscan,

Array enter each of these (enter using Ctrl-Shift-Enter)

First filled cell of row 2:
=ADDRESS(ROW(2:2),MIN(IF(2:2<"",COLUMN(2:2),256)) )

Last filled cell of row 2:
=ADDRESS(ROW(2:2),MAX(IF(2:2<"",COLUMN(2:2),0)))

HTH,
Bernie
MS Excel MVP


"Skyscan" wrote in message
...
Hello!

I have data in a single row array. The data is not contiguous such
that
there are holes in the data. Several cells may be blank in the first
few
cells, in the middle cells, or in the last cells. I need to identify
which
cell is the one where the data begins. In addition, I need to
identify which
cell is the one where the data ends. It is not necessary to locate
"holes"
in the data set. I would like to use functions only to identify
these first
and last cells.

Thank you very much!!

Tom

Thanks Bernie. I believe I did as you stated. I tried experimenting with
the position of the numbers in row 2. The result of the function shows
either $A$2 or $IV$2. When I go to Insert, Function and I look at the
template box ... I see the result is correct- it shows the location of the
cell where the data begins. Why would it only show correctly there, but not
as a result in the cell?

I'm sorry to ask another question here, but is there a function I can use to
find the contents of the first populated cell in that row?

Thanks!

Tom

"Bernie Deitrick" wrote:

Tom,

Instead of typing in the formula and pressing <Enter, you need to type the
formula in, hold down the <Ctrl key, hold down the <Shift key, and while
those two keys are held down, press the <Enter key. This enters the
formula as an array formula, which is a special formula type that evaluates
each cell in the range on a cell by cell basis, rather than all at once.

If you cannot get the formulas to work, I will send you a sample workbook
with the formulas working, if you post your email address or contact me
privately.

HTH,
Bernie
MS Excel MVP


"Skyscan" wrote in message
...
Sorry, but I was not able to get it to work. Please explain the comment
about array entering using Cntrl-Shift-Enter.

Thanks!

Tom

"Skyscan" wrote:

Thank you very much!!

Tom

"Bernie Deitrick" wrote:

Skyscan,

Array enter each of these (enter using Ctrl-Shift-Enter)

First filled cell of row 2:
=ADDRESS(ROW(2:2),MIN(IF(2:2<"",COLUMN(2:2),256)) )

Last filled cell of row 2:
=ADDRESS(ROW(2:2),MAX(IF(2:2<"",COLUMN(2:2),0)))

HTH,
Bernie
MS Excel MVP


"Skyscan" wrote in message
...
Hello!

I have data in a single row array. The data is not contiguous such
that
there are holes in the data. Several cells may be blank in the first
few
cells, in the middle cells, or in the last cells. I need to identify
which
cell is the one where the data begins. In addition, I need to
identify which
cell is the one where the data ends. It is not necessary to locate
"holes"
in the data set. I would like to use functions only to identify
these first
and last cells.

Thank you very much!!

Tom