This article applies to all versions of Microsoft Excel For Windows®, unless stated otherwise.

Author(s): Frank Kabel & Bob Phillips
© Copyright, 2004, Frank Kabel & Bob Phillips

Date Written: 1st September 2004
Last Updated: 28th September 2004

Article Summary:

Finding specific values in a list or a data range is one of the more common tasks while developing a spreadsheet application. Many people are quite familiar using the standard Excel functions VLOOKUP/HLOOKUP or a combination of INDEX and MATCH to locate a value in a data table depending on a condition. A different problem is the location of the last filled element within a range (either in a column or a row). This paper discusses that problem, and offers various techniques, formulae, and consideration to apply.

Article Contents:

This paper is structured in the following way:

Introduction
Getting The Last Numeric Element In A Column/Row
Getting The Last Text Element In A Column/Row
Getting The Last Numeric or Text Element In A Column/Row
Worksheet Benchmarks
Worksheet Function Summary
Last Value In VBA
Acknowledgements and References

This article assumes familiarity with the following:

The Excel Application
Worksheet functions, such as LOOKUP, INDEX, MATCH, etc
An understanding of what an array formula is, and how to create one
Visual Basic For Applications (VBA) programming concepts (for the section Last Value In VBA)

Introduction

As Excel has no dedicated function to get the last value in a range the following pages describe how to use the built-in functions for this purpose, separately addressing numeric and text data types, or any data type.

This paper also has a short section on VBA solutions for getting the last value in a range, but mainly focuses on worksheet function based solutions.

Within Excel there is not a single formula to get the last element within a column or a row, but it can be achieved by a combination of worksheet functions within a variety of differing requirements. This paper shows a number of the combinations, and discusses the strengths and weaknesses of each approach, and where it might be applied.

The fact that this is achieved in Excel by combining a number of functions can be seen as a strength of Excel (a strong base set of functions that provide a large degree of extensibility), or a weakness (lack of commonly required functions), depending upon your viewpoint.

Which formula you choose can depend on the following factors:

What kind of data element you are searching for (numeric values, text values, any values, etc.)
Searching within a column or a row
How to handle error values within your range
Formula efficiency/performance
Personal taste

What follows is a compilation of several working formulas with a description to help you to choose depending on your needs.

In general the description focuses on getting the last value from a column. The formula to get the last value from a row is given but generally not described.

The examples mainly work upon a full column (A:A), or the best part of (A1:A65535), but it should be remembered that the formulae will apply to any range such as A1:An, where n is a row number between 1 and 65535.

Most formulae can deal with a full column , but the array formulae are restricted to a range that is a subset of a full column. This is a typical restriction of array formulas not being able to deal with 65536 elements (the maximum number of rows Excel supports), but only 65535 elements (the largest value which could be stored in an unsigned integer variable). Thus you have to use a range such as A1:An where n is an integer between 1 and 65535. This won't get the correct last element in a column if this resides in the row 65536 (but in most cases this will not be an issue). In all cases, a full row (1:1) can be specified as this fits easily with the limits of an unsigned integer variable.

Getting Last Numeric Value In Range

In some cases you only need to find the last numeric value, ignoring any non-numeric values.

#001 - Searching For Largest Possible Number Using INDEX/MATCH
#002 - Searching For Largest Possible Number Using LOOKUP
#003 - An INDEX Array Formula
#004 - LOOKUP Alternative

#001 - Searching For Largest Possible Number Using INDEX/MATCH

For the last numeric element in a column, a simple INDEX or LOOKUP will work
=INDEX(A:A,MATCH(9.99999999999999E307,A:A))

Similarly, for the last numeric element in a row:
=INDEX(1:1,MATCH(9.99999999999999E307,1:1))

Fig. 1.

How it works:

The INDEX/MATCH formula searches for the value 9.99999999999999E307.
This value is the highest value that can be represented in Excel. Therefore this formula returns the last numeric value that is smaller than or equal to this number. It is unlikely that the spreadsheet will contain this value, so it will find the last value that is smaller, that is the last numeric value (as all the numeric values will be smaller).
If you do not want to have this number hardcoded in your formulas, you can define a workbook name (e.g. 'BigNumber') and assign this value to the defined name. You would then use that name in a formula, such as
=INDEX(A:A,MATCH(BigNumber,A:A))

Constraints:

The INDEX/MATCH formula can deal with a range such as A:A as it does not create an array within the formula
If no numeric entry exists within the range, the MATCH formula will return the #N/A error.
This solution ignores error values. If the range contains, for example, #DIV/0 errors the formula will not highlight this fact, but will return the last non-error value.

#002 - Searching For Largest Possible Number Using LOOKUP

An alternative is a similar approach using the LOOKUP function
=LOOKUP(9.99999999999999E307,A:A)

Similarly, for the last numeric element in a row:
=LOOKUP(9.99999999999999E307,1:1)

Fig. 2.

How it works:

This works in the same fundamental way as solution #001.
The main difference is simply in using one function call, LOOKUP, to the previous formula's two, INDEX and MATCH.

Constraints:

The LOOKUP formula can deal with a range such as A:A as it does not create an array within the formula
If no numeric entry exists within the range the LOOKUP formula will both return the #N/A error.
This solution ignores error values. If the range contains, for example, #DIV/0 errors the formula will not highlight this fact, but will return the last non-error value.

#003 - An INDEX Array Formula

An alternative solution can be achieved with the following array formula
=INDEX(A:A,MAX(IF(ISNUMBER(A1:A65535),ROW(A1:A65535))))
or
=INDEX(1:1,1,MAX(IF(ISNUMBER(1:1),COLUMN(1:1))))

As this is an array formula, it is committed with Ctrl-Shift-Enter.

Fig. 3.

How it works:

The inner IF statement is used to build an array of row numbers for each of the cells containing a number
The highest row (or column) is then found with the MAX function
The maximum value is passed to the INDEX function to return the value in that row (or column).

Constraints:

In the column variation, the ISNUMBER and ROW functions have to be passed an explicit range, it does not work with a complete column
This is an array formula
There are multiple function calls in this solution, thereby impacting performance
This solution ignores error values. If the range contains, for example, #DIV/0 errors this formula will not highlight this fact, but will return the last non-error value.

#004 - LOOKUP Alternative

There's an additional alternative that doesn't ignore error values, but highlights them in the result returned:
=LOOKUP(MAX(A:A)+1,A:A)
or
=LOOKUP(MAX(1:1)+1,1:1)

Fig. 4.

How it works:

The MAX(A:A)+1 statement sets up a value that is not found within the range, and so serves the same function as 'BigNumber' in Solution #001
This is then used in the LOOKUP function in the same way, so finding the last value less than the lookup value, which is the last numeric value.

Constraints:

This variation requires another function call (the MAX function) processing the entire range, thereby impacting performance
This solution highlights errors. If the range contains, for example, #DIV/0 errors the formula will also return a #DIV/0 error.

Getting Last Text Value In Range

Using the techniques described for finding numeric values you could use similar formulas to return the last text entry within a range.

#005 - Searching For Largest Text Value Using INDEX/MATCH
#006 - Searching For Largest Text Value Using LOOKUP
#007 - An INDEX Array Formula
#008 - An INDEX Array Formula Alternative

#005 - Searching For Largest Text Vale Using INDEX/MATCH

=INDEX(A:A,MATCH(REPT("Z",255),A:A))
For the last text element in a column

or
=INDEX(1:1,MATCH(REPT("Z",255),1:1))

For the last text element in a row

Fig. 5.

How it works:

The INDEX/MATCH formula uses a string consisting of 255 'Z' characters to find the last text entry
For Excel, this string evaluates to the 'largest' string value
It is unlikely that the spreadsheet will contain this value, so it will find the last value that is smaller, that is the last text value (as all the text values will be smaller)
Again, the 255 Z's could be setup as a workbook name and use this name in the formulae for flexibility.

Constraints:

You cannot use the function REPT(CHAR(255),255), as the largest value. Whilst you might suspect that Excel evaluates this to the 'largest' string value, Excel evaluates the following formula:
=REPT("Z",255)<REPT(CHAR(255),255)
to FALSE
The LOOKUP and INDEX/MATCH formula can also deal with a range such as A:A as it does not create an array within the formula
If no numeric entry exists within the range the LOOKUP formula and the MATCH function will return the #NA error
These solutions ignore error values. If the range contains, for example, #DIV/0 errors the formulas will not highlight this fact, but will return the last non-error value.

#006 - Searching For Largest Possible Number Using LOOKUP

=LOOKUP(REPT("Z",255),A:A)
For the last text element in a column

=LOOKUP(REPT("Z",255),1:1)
For the last text element in a row

Fig. 6.

How it works:

This works in the same fundamental way as solution #005
The main difference is simply in using two function calls, LOOKUP and REPT, to the previous formula's three, INDEX, MATCH and REPT.

Constraints:

You cannot use the function REPT(CHAR(255),255), as the largest value
The LOOKUP formula can also deal with a range such as A:A as it does not create an array within the formula
If no numeric entry exists within the range the LOOKUP formula will return the #NA error
These solutions ignore error values. If the range contains, for example, #DIV/0 errors the formulas will not highlight this fact, but will return the last non-error value.

#007 - An INDEX Array Formula

An alternative solution can be achieved with the following array formula
=INDEX(A:A,MAX(IF(ISTEXT(A1:A65535)*(A1:A65535<>""),ROW(A1:A65535))))
or
=INDEX(1:1,1,MAX(IF(ISTEXT(1:1)*(1:1<>""),COLUMN(1:1))))

As this is an array formula, it is committed with Ctrl-Shift-Enter.

Fig. 7.

How it works:

The inner IF statement is used to build an array of row numbers for each of the cells containing text but not being empty
The highest row (or column) is then found with the MAX function
The maximum value is passed to the INDEX function to return the value in that row (or column).

Constraints:

In the column variation, the ISTEXT and ROW functions have to be passed an explicit range, it does not work with a complete column
This is an array formula
There are multiple function calls in this solution, thereby impacting performance
This solution highlights errors. If the range contains, for example, #DIV/0 errors the formula will also return a #DIV/0 error

#008 - An INDEX Array Formula Alternative

To ignore errors in the range, this slight variation on the array formula works.
=INDEX(A:A,MAX(IF(ISTEXT(A1:A65535),ROW(A1:A65535))))
or
=INDEX(1:1,1,MAX(IF(ISTEXT(1:1),COLUMN(1:1))))

As this is an array formula, it is committed with Ctrl-Shift-Enter.

Fig. 8.

How it works:

The inner IF statement is used to build an array of row numbers for each of the cells containing text
The highest row (or column) is then found with the MAX function
The maximum value is passed to the INDEX function to return the value in that row (or column).

Constraints:

In the column variation, the ISTEXT and ROW functions have to be passed an explicit range, it does not work with a complete column
This is an array formula
There are multiple function calls in this solution, thereby impacting performance
This solution ignores error values. If the range contains, for example, #DIV/0 errors the formula will not highlight this fact, but will return the last non-error value.

Getting Last Numeric or Text Value In Range

Searching for any data type within the range gives rise to another set of formulae.

#009 - Using OFFSET And COUNTA
#010 - Using An INDEX Array Formula
#011 - Using An INDEX Array Formula - 2
#012 - Using A Non-Array LOOKUP Formula
#013 - Using A Non-array INDEX/MATCH Formula
#014 - Using A Non-array INDEX/MATCH Formula

#009 - Using OFFSET And COUNTA

A simple and common formula to find any last value is to use a combination of OFFSET and COUNTA. e.g.
=OFFSET($A$1,COUNTA(A:A)-1,0)
for searching within a column

or
=OFFSET($A$1,0,COUNTA(1:1)-1)
for searching within a row.

Fig. 9.

How it works:

The COUNTA function calculates how many cells are not empty within the defined range. Looking at the above example COUNTA(A:A) returns 9. As you can see it counts all filled cells regardless of whether they contain numeric values, strings, formulae or errors
The first parameter of the OFFSET function determines the starting point of the data range (the top or left-most cell)
The COUNTA result is reduced by 1 as the 2nd / 3rd parameter of the OFFSET function (number of rows/columns) determines how many rows/columns to 'offset' from the initial cell, not how many rows/columns are used
In the example, the last value is in cell A10. Thus, the cell reference A1 has to be 'offset' by 9 rows. As COUNTA returns 10 for this range, subtracting '1' is required to get the correct result. Without subtracting 1 you would get the first empty cell in a range. Note that in this example actually returns the value 'geh' as one of the cells in the range is empty. If none are empty it works correctly.

Constraints:

The range of data has to be contiguous, that is no blank cells within the range. If there are embedded blanks, these will not be counted by COUNTA, so offsetting from the start cell will find a cell within the range, not the last filled cell
COUNTA counts cells as non-empty if they contain a formula which returns "". So although the cell looks empty, it is not really a blank cell, and is therefore counted
If the entire range is empty, this formula can produce an #REF error if the range includes row 1 or column A, or 0 if it starts at some other row/column.

In this case, COUNTA returns '0' as no cell is filled. Subtracting '1' from this result in '-1'.

The OFFSET function tries to 'shift' the initial cell back one row or column. If the range includes the first row or column, there is no such cell, hence the #REF error. If the ranges starts at some other row or column, it returns an empty cell value
This solution ignores error values. If the range contains, for example, #DIV/0 errors the formula will not highlight this fact, but will return the last non-error value.

#010 - Using An INDEX Array Formula

Solution #009 cannot handle embedded blank cells in the range, so if this is a consideration , a different solution is required.

One solution is using a combination of INDEX, ISBLANK & ROW (or COLUMN if processing a single row).
=INDEX(A:A,MAX(IF(ISBLANK(A1:A65535),0,ROW(A1:A65535))))
for searching within a column

or
=INDEX(1:1,1,MAX(IF(ISBLANK(1:1),0,COLUMN(1:1))))
for searching within a row.

These are array formulae, so they are committed with Ctrl-Shift-Enter.

Fig. 10.

How it works:

The inner IF statement is used to build an array of row numbers for each of the cells containing a value or a formula
The empty cells are given a row number of 0
The highest row (or column) is then found with the MAX function, avoiding the blank cells as their row (or column) number will be 0
The maximum value is passed to the INDEX function to return the value in that row (or column).

Constraints:

In the column variation, the ISBLANK and ROW functions have to be passed an explicit range, it does not work with a complete column
This is an array formula
If the entire column (or row) is empty this formula will return '0'. The MAX function returns '0' in this case. So the above formula would effectively be reduced to
=INDEX(A:A,0)
When setting the 2nd (or 3rd) parameter of INDEX to zero, it returns the array of values for the entire column or row, respectively. As none of these cells is filled, INDEX will return '0' for a blank cell
This solution ignores error values. If the range contains, for example, #DIV/0 errors the formula will not highlight this fact, but will return the last non-error value.

#011 - Using An INDEX Array Formula - 2

If you want to highlight any errors within your range you can slightly adapt the above formulae to
=INDEX(A:A,MAX(IF(A1:A65535<>"",ROW(A1:A65535))))
or
=INDEX(1:1,1,MAX(IF(1:1<>"",COLUMN(1:1))))

These are array formulae, so they are committed with Ctrl-Shift-Enter.

Fig. 11.

How it works:

This works in the same fundamental way as solution #010
The main difference is that testing a cell for "" will return the value if there is an error, whereas the ISBLANK will return FALSE, thereby highlighting error values

Constraints:

In the column variation, the MAX and ROWf functions have to be passed an explicit range, it does not work with a complete column
This is an array formula
This solution highlights errors. If the range contains, for example, #DIV/0 errors the formula will also return a #DIV/0 error.

#012 - Using A Non-Array LOOKUP Formula

A non-array solution can be found using the LOOKUP function
=LOOKUP(2,1/(1-ISBLANK(A1:A65535)),A1:A65535)
for searching within a column
or
=LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)
for searching within a row.

Fig. 12.

How it works:

This formula uses LOOKUP in its vector syntax form, with the lookup value as the first parameter, the lookup vector as second, and the result vector as the last parameter
The most interesting part of this formula is the lookup vector (the 2nd parameter). The formula element
1/(1-ISBLANK(A1:A65535))
in this example returns the following array
{1;1;1;1;1;1;1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;...;#DIV/0!}

that is, the ISBLANK function returns an array of TRUE (blank cell) or FALSE (non-blank cell) values.

Subtracting this from 1 converts the array to an array of 0 (blank) or 1 (non-blank) values.

Dividing 1 by this array then returns an array of #DIV/0 (blank) or 1 (non-blank) values
The LOOKUP searches for the value '2' within the array (which now consists only of '1' and #DIV/0 values). The LOOKUP will not find this value, so it matches the last value that is less than or equal to lookup value. This is the last '1' within the range which represents the last filled cell
This type of formula can be used for a lot of similar problems using the second parameter to create a lookup vector consisting of either '1' or '#DIV/0' errors by setting the Boolean expression accordingly.

Constraints:

In the column variation, the ISBLANK function and the result vector have to be passed an explicit range, it does not work with a complete column
If the entire column / row is empty this formula will return an #N/A error as the LOOKUP function finds no value that is smaller or equal to the lookup value (the lookup vector consists only of #DIV/0 errors)
This formula is quite slow
This solution ignores error values. If the range contains, for example, #DIV/0 errors the formula will not highlight this fact, but will return the last non-error value.

#013 - Using A Non-array INDEX/MATCH Formula

If you don't want to return formula results such as "" as last element within your range you can slightly adapt the above formulas to
=LOOKUP(2,1/(A1:A65535<>""),A1:A65535)
for searching within a column

or
=LOOKUP(2,1/(1:1<>""),1:1)
for searching within a row.

Fig. 13.

How it works:

This works in the same fundamental way as solution #009
The main difference is that testing a cell for "" will return the value if there is an error, whereas the ISBLANK will return FALSE.

Constraints:

In the column variation, the test range and the result vector have to be passed an explicit range, it does not work with a complete column
This solution highlights errors. If the range contains, for example, #DIV/0 errors the formula will also return a #DIV/0 error.

#014 - Using A Non-Array LOOKUP Formula

The previous two solutions (#009 and #010) have one big disadvantage, they are slow As an alternative you can use the following non-array formula
=INDEX(A:A,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9.99999999999999E+307},A:A)))
for searching within a column
or
=INDEX(1:1,1,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9.99999999999999E+307},1:1)))
for searching within a row.

Fig. 14.

How it works:

This formula uses an array of values as the lookup value for the MATCH function. The MATCH function returns an array of the found elements. That is one value for each of the two lookup values. In this example the MATCH formula returns {9;10}
- 9 for the last text value in the range (cell A9) and
- 10 for the last numeric entry in this range (cell A10)
The MAX function then evaluates the larger of the two values of this returned array (row index 10) and uses this as parameter for the INDEX function.

Constraints:

You have to hardcode the two lookup values within the MATCH function. that is you can't replace "zzzzzzzzzz" for example with REPT("Z",255)
It is not possible to replace the two values with defined names
If the entire column / row does not contain any text or numeric values this formula will return an #N/A error as the MATCH function finds no value that is smaller or equal to the lookup value
This solution ignores error values. If the range contains, for example, #DIV/0 errors the formula will not highlight this fact, but will return the last non-error value.

Worksheet Functions Benchmarks

In many instances, formula performance will not be an issue, and so the choice of which formula to use would be made on the values to be tested, error handling requirements, nested function restrictions and personal preference.

In some situations, the complexity of the spreadsheet will be such that it requires a number of formulas, and inevitably, more formulas will mean a slow-down in the re-calculation of the spreadsheet. In these cases, performance becomes an issue, so you need to understand the relative performance benchmarks of the differing formulas.

The benchmark for the formulas described in this paper was created using FastExcel V2, using the following environment:-

The spreadsheet consisted of 60,000 random text, numeric, blank and error values in column A
Each formula was copied 500 times. For this benchmark, only the column oriented formulas were used
The benchmark was run under Excel 2003 / Windows 2000 on a 2 GHZ Pentium processor with 1 GB Ram.

The 13 formulae given above are benchmarked. In the table, Fig. 14, which shows the result of the benchmark, they are referenced by the id number, which are all listed below with the appropriate formula:-

001 - INDEX(A:A,MATCH(9.99999999999999E307,A:A))
002 - LOOKUP(9.99999999999999E307,A:A)
003 - INDEX(A:A,MAX(IF(ISNUMBER(A1:A65535),ROW(A1:A65535))))
004 - LOOKUP(MAX(A:A)+1,A:A)
005 - INDEX(A:A,MATCH(REPT("Z",255),A:A))
006 - LOOKUP(REPT("Z",255),A:A)
007 - INDEX(A:A,MAX(IF(ISTEXT(A1:A65535)*(A1:A65535<>""),ROW(A1:A65535))))
008 - INDEX(A:A,MAX(IF(ISTEXT(A1:A65535),ROW(A1:A65535))))
009 - OFFSET($A$1,COUNTA(A:A)-1,0)
010 - INDEX(A:A,MAX(IF(ISBLANK(A1:A65535),0,ROW(A1:A65535))))
011 - INDEX(A:A,MAX(IF(A1:A65535<>"",ROW(A1:A65535))))
012 - LOOKUP(2,1/(1-ISBLANK(A1:A65535)),A1:A65535)
013 - LOOKUP(2,1/(A1:A65535<>""),A1:A65535)
014 - INDEX(A:A,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9.99999999999999E+307},A:A)))

Fig. 14.

Worksheet Functions Summary

This section summarises the functions and formula combinations described above, and the various facts, attributes, etc. of each, and then summarises what has been described before.

Solution Id	Numeric (N) / Text (T) / Any (A)	Full Column Handled (Y/N)?	Conti- guous (Y/N)?	Ignore (I) or Highlight (H) Errors	Array Formula (Y/N)?	Function Calls	Benchmark Rating Group/Overall
001	N	Y	N	I	N	2	2/2
002	N	N	N	I	N	1	1/1
003	N	N	N	I	Y	5	4/10
004	N	Y	N	H	N	2	3/7

005	T	Y	N	I	N	3	2/4
006	T	Y	N	I	N	2	1/3
007	T	N	N	H	Y	5	4/14
008	T	N	N	I	Y	5	3/9

009	A	Y	Y	I	N	2	2/6
010	A	N	N	I	Y	5	4/11
011	A	N	N	H	Y	4	3/8
012	A	N	N	I	N	2	6/13
013	A	N	N	I	N	1	5/12
014	A	Y	N	I	N	3	1/5

Fig. 15.

Conclusions

General observations
- The worst performance is achieved with the error highlighting text search formula (007), over twice as slow as any other
- The LOOKUP formulas (012 and 013) for searching for any value have the worst overall performance. They are even slower than array formula alternatives
- The array formulas (003, 008, 010 and 011) are nearly as slow as the LOOKUP formulas
- The remaining formulas (001, 002, 005, 006 and 013) are all more than 200 times faster than the other formulas.
Searching for the last numeric value
- The best was measured for the LOOKUP formula (002). The other INDEX/MATCH formula (001) using a similar approach is nearly as fast as the LOOKUP variant
- Both formulas (001 and 002) use the same approach, that is searching for the 'big number' 9.99999999999999E307
- The INDEX/MAX/ISNUMBER formula combination (003) is more than 360 times slower than the fastest LOOKUP formula (002)
- The alternative LOOKUP/MAX that highlights errors is over 20 times as slow as the fastest formula (002)
- The initial INDEX/MATCH (001), the LOOKUP (002) formulas, and the INDEX/MAX/ISNUMBER ()03) formula all ignore error values, whereas the LOOKUP/MAX (004) formula highlights errors.
Searching for the last text value:
- The INDEX/MATCH/REPT and the LOOKUP/REPT formulas (005 and 006) beat the array INDEX/MAX/ISTEXT formula (007) solution by more than 300 times
- The INDEX/MATCH/REPT, the LOOKUP/REPT and the INDEX/MAX/ISTEXT formula (005, 006 and 008) all ignore error values.
4. Searching for any value:
- In this case, using either the array formula INDEX/MAX/ISBLANK (010) or either of the LOOKUP formula combinations (012 and 013) will result in a slow performance
- The Fastest formula is the INDEX/MAX/MATCH combination (014). This is consistent with the results from the other search functions for finding only numeric or text values. It is approximately two times slower than the variants for finding a single data type but it is still nearly 200 times faster than the other 3 formula alternatives for finding the last value of either text or numeric data< /li>
- The OFFSET formula ((009), the two array INDEX formulas (010 and 011), and the INDEX/MAX/MATCH combination (014) all ignore error values. The INDEX/MAX/ROW formula (011) and the LOOKUP formula (013) both highlight errors.

Recommendations

In general, recommendations are dificult to make, as every individual need will differ, and the poor solution in one situation may be ideal in another.

The conclusions above are provided to help you to choose the appropriate formula for your particular circumstances.

Last Value In VBA

Simple Last Value

Finding the last value in a column is straight-forward. Assuming that the column to be searched is column A, this code will return the final cell

Dim cLastRow As Long
Dim oLast As Range

    cLastRow = Cells(Rows.Count,"A").End(xlUp).Row
    Set oLast = Range("A" & cLastRow)
    Activecell.Value = oLast.Value

This works by starting at the bottom-most row in the column, Cells(Rows.Count,"A"), and then moving up to the first found non-empty cell, End(xlUp), and get the row number for that cell, .Row.

This row number is then used in the Range statement to get that cell object.

Similalrly, to get the last value in a row 1, use

Dim cLastCol As Long
Dim oLast As Range

    cLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Set oLast = Cells(1,cLastCol)
    Activecell.Value = oLast.Value

This is a similar technique, going to the last column in the row, and moving back left.

Note that this uses Cells rather than Range as the column returned is a number.

This can be turned into a generic function that works for either row, or column.

Function fzLastValue(RangeId As Variant, Optional byColumn As Boolean = True)
Dim cLast As Long
Dim oLast As Range

    Application.Volatile

    If byColumn Then
        cLast = Cells(Rows.Count, RangeId).End(xlUp).Row
       Set oLast = Range(RangeId & cLast)
    Else
        cLast = Cells(RangeId, Columns.Count).End(xlToLeft).Column
        Set oLast = Cells(RangeId, cLast)
    End If

    fzLastValue = oLast.Value

End Function

Application.Volatile is needed if this function is to be used as a worksheet function to ensure that the calling cell gets updated if the range being searched is changed in any way.

This will find the last value, regardless of whether it is text, numeric, or an error value.

Last Numeric Or Text Value

Getting the last numeric or text value is somewhat more difficult. One way is to use some of the worksheet functions described above, called from VBA.

This can all be amalgamated as before intro a single function, as before, which can be caled as a worksheet function.

Function fzLastValue(RangeId As Variant, _
                     Optional byColumn As Boolean = True, _
                     Optional ValueType As String = "A", _
                     Optional Highlight As Boolean = False)
Dim cLast As Long
Dim oLast As Range

    Application.Volatile

    Select Case UCase(ValueType)

        Case "N":
            If byColumn Then
                If Highlight Then
                    fzLastValue = LastError(rng, byColumn, ValueType)
                Else
                    fzLastValue = Cells(Evaluate("MATCH(" & _
                     "9.99999999999999E307," & rng.Address & ")"), rng.Column)
                End If
            Else
                If Highlight Then
                    fzLastValue = LastError(rng, byColumn, ValueType)
                Else
                    fzLastValue = Cells(rng.Row, Evaluate("MATCH(" & _
                       "9.99999999999999E307," & rng.Address & ")")).Value
                End If
            End If

        Case "T":
            If byColumn Then
                If Highlight Then
                    fzLastValue = LastError(rng, byColumn, ValueType)
                Else
                    fzLastValue = Cells(Evaluate("MATCH(REPT( & _
                        ""Z"",255)," & rng.Address & ")"), & _
                        rng.Column).Value
                End If
            Else
                If Highlight Then
                    fzLastValue = LastError(rng, byColumn, ValueType)
                Else
                    fzLastValue = Cells(rng.Row, Evaluate("MATCH(REPT( & _
                       ""Z"",255)," & rng.Address & ")")).Value
                End If
            End If

        Case Else:
            If byColumn Then
                If Highlight Then
                    fzLastValue = LastError(rng, byColumn, ValueType)
                Else
                    cLast = Cells(Rows.Count, rng.Column).End(xlUp).Row
                    fzLastValue = Cells(cLast, rng.Column).Value
                End If
            Else
                If Highlight Then
                    fzLastValue = LastError(rng, byColumn, ValueType)
                Else
                    cLast = Cells(rng.Row, Columns.Count) & _
                    	.End(xlToLeft).Column
                    fzLastValue = Cells(rng.Row, cLast).Value
                End If
            End If

    End Select

End Function

Private Function LastError(rng As Range, _
                           byColumn As Boolean, _
                           ValueType As String)
Dim cLast As Long
Dim vText

    Select Case UCase(ValueType)

        Case "N":
            If byColumn Then
                vText = Evaluate("LOOKUP(MAX(" & rng.Address & ")+1," & _
                    rng.Address & ")")
                LastError = vText
            Else
                LastError = Evaluate("LOOKUP(MAX(" & rng.Address & ")+1," & _
                    rng.Address & ")")
            End If

        Case "T":
            vText = Evaluate("LOOKUP(MAX(" & rng.Address & ")+1," & _
                rng.Address & ")")
            If VarType(vText) <> 10 Then
                vText = Evaluate("LOOKUP(REPT(""Z"",255)," & rng.Address & ")")
            End If
            LastError = vText

        Case Else:
            vText = Evaluate("LOOKUP(MAX(" & rng.Address & ")+1," & _
                rng.Address & ")")
            If VarType(vText) <> 10 Then
                If byColumn Then
                    cLast = Cells(Rows.Count, rng.Column).End(xlUp).Row
                    vText = Cells(cLast, rng.Column).Value
                Else
                    cLast = Cells(rng.Row, Columns.Count).End(xlToLeft).Column
                    vText = Cells(rng.Row, cLast).Value
                End If
            End If
            LastError = vText

    End Select

End Function

This is not necessarily intended as a complete working solution UDF, but is more to show how to achieve each of the different solutions, to be deployed as befits in VBA.

The performance of this function is very poor, so that should deter deployment in a live environment, but the techniques used are still valid.

References

Acknowledgments

These formula combinations have been harvested from a number of sources, reference websites, solutions provided in the Excel newsgroups, and the authors own development experiences. This means that you get a comprehensive toolkit presented that you can utilise, with most of the research already done for you.

With this extensive collation of items, acknowledgement of the contribution of all of the individuals in the development of these formulas is not possible, but we would give mention to the contribution of the following individuals (in no order other than alphabetic)

Aladin Akyurek
Harlan Grove
Jason Morin
Peo Sjoblom

In addition, mention is given to Charles Williams for the FastExcel utility used in the benchmarks.

Downloads

This page can be downloaded as a zipped Word document here (307Kb)

Article Summary:

Article Contents:

Introduction

Getting Last Numeric Value In Range

#001 - Searching For Largest Possible Number Using INDEX/MATCH

How it works:

Constraints:

#002 - Searching For Largest Possible Number Using LOOKUP

How it works:

Constraints:

#003 - An INDEX Array Formula

How it works:

Constraints:

#004 - LOOKUP Alternative

How it works:

Constraints:

Getting Last Text Value In Range

#005 - Searching For Largest Text Vale Using INDEX/MATCH

How it works:

Constraints:

#006 - Searching For Largest Possible Number Using LOOKUP

How it works:

Constraints:

#007 - An INDEX Array Formula

How it works:

Constraints:

#008 - An INDEX Array Formula Alternative

How it works:

Constraints:

Getting Last Numeric or Text Value In Range

#009 - Using OFFSET And COUNTA

How it works:

Constraints:

#010 - Using An INDEX Array Formula

How it works:

Constraints:

#011 - Using An INDEX Array Formula - 2

How it works:

Constraints:

#012 - Using A Non-Array LOOKUP Formula

How it works:

Constraints:

#013 - Using A Non-array INDEX/MATCH Formula

How it works:

Constraints:

#014 - Using A Non-Array LOOKUP Formula

How it works:

Constraints:

Worksheet Functions Benchmarks

Worksheet Functions Summary

Conclusions

Recommendations

Last Value In VBA

Simple Last Value

Last Numeric Or Text Value

References

Further Reading

Acknowledgments

Downloads