The last filled cell in a range

[The Narcissist]

Hi Everyone,

I'm working on some HR reports on Excel. The raw employee data is kept in
one sheet and I pull out information from that sheet using VLookup by looking
up the employee code. However, the employee count keeps changing on a regular
basis. To make sure that the vlookup function goes through the entire data, I
have kept all rows till 65536 in the data range. This makes the reports very
slow. Is there any other way to ensure that the entire data is looked into
without me having to change the range in the vlookup function everytime there
is an addition or deletion in the raw data? I mean to ask if there is any
function that returns the last filled cell in a range?

Any help would be greatly appreciated.

Thanks,

Sam

 

[Roger Govier]

Hi Sam

Create a Dynamic named range.
Supposing your data is in cells A1 through M500 at present, and you will be
continually adding new rows.
Insert>Name>Define>Name MyData Refers to =$A$1:INDEX($M:$M,COUNTA($A:$A))

Then your Vlookup formula will become
=VLOOKUP(cell,myData,x,0)
where cell is the cell address for your present lookup value, and x is the
offset that you use at present.

 

[The Narcissist]

Thanks Roger. This looks like it should work. However, I'm off for a meeting
right now. Will check tomorrow and revert back with results.

Thanks again

 

[The Narcissist]

Hi Roger,

The meeting got cancelled. I tried your suggestion. Worked fine for one
dynamic range. But for the other one, which is quite large, it somehow ends
up not selecting the last 6 rows. The data is from cell A8 to Q8425. Although
currently, only columns A thru E are populated right through to row 8425. I
defined a range called DATARANGE that refers to

=$A$8:INDEX($Q:$Q,COUNTA($A:$A))

When I type DATARANGE in the address box, it selects all cells from A8 thru
Q8419. Any thoughts as to why this might be happening?

Thanks,

Sam

 

[Bernie Deitrick]

That happens because your COUNTA doesn't take into account the blanks above row 8 when counting -
you need to offset by the number of blanks at the top of column A:

=$A$8:INDEX($Q:$Q,COUNTA($A:$A)+6)


HTH,
Bernie
MS Excel MVP

 

[Roger Govier]

Hi Sam

I was assuming Headers in row 1, and making the Count on Column A, again
assuming that would have the maximum rows filled, even if rows weren't
completed in other columns.
to get the result you are getting, there must be something in at least 2 of
the cells A1:A7.
I guess that your header is in A7, but there is one item within A1:A6
As a result, COUNTA($A:$A) is returning a value which is correct, there is
data in all but 6 of the cells.

You would need to adjust the formula to add 6 to the value returned by
Counta.
=$A$8:INDEX($Q:$Q,COUNTA($A:$A)+6)

I tend to include the headers in my named ranges, (as I am often using the
data in Pivot Tables which require a header row as well), so I would use
=$A$7:INDEX($Q:$Q,COUNTA($A:$A)+5)
It will make no difference to your Vlookup's.

 

[The Narcissist]

Thanks Bernie and Roger. I acted like such a lame duck on that one. Should've
seen that. Anyways, thanks a million.

Sam