Excel - Obtaining the Last Item in a Column of Cells

Asked By Jason Pawloski on 18-Oct-08 12:08 AM
Hi all,

I have a variable-length list of letters in a column in the Excel
sheet. Sometimes the last element of the list is of interest to me for
calculations, that is, the largest numbered cell that is not empty. I
can't simply reference the cell because the list is variable and will
change many, many times in the future.

I am wondering if there is a way to just grab the letters from the
last column? I thought I had a pretty clever solution for this - I'd
concatenate the entire list (the list can't be more than 256 elements,
so I would concat, for example, A1:A256) and then take that text
string and use "RIGHT" to grab the rightmost characters. Every element
of the list is two letters so that'd be easy.

However I was dismayed to find that concat cannot handle cell
references of the form A1:A256. Since this text is going to be
embedded in much larger formulae, explicitly writing out
A1&A2&A3&A4&...&A256 is suboptimal.

Any suggestions? Thanks in davance.

Don Guillett replied on 16-Oct-08 02:05 PM
=match("zzzzzzzz",a:a) will find the last row with text in col a
then use within and index formula

Don Guillett
Microsoft MVP Excel
SalesAid Software
T. Valko replied on 16-Oct-08 02:09 PM
Try this:


That will return the *last TEXT entry* from column A. Note that this will
also return a formula blank (which *is* a text value) if it's the last text
entry in the range.

Microsoft Excel MVP
Jason Pawloski replied on 18-Oct-08 12:08 AM

Thank you very much!
Thomas Hardy replied on 16-Oct-08 02:18 PM

This formula will give you the last item in the list assuming column A
contains text and not numbers:


Make sure that 'zzzz' is "higher" than any other value likely to appear in
your list. If you think that 'zzzz' might appear one day then add a few more
z's for luck.

By the way, I'm just dipping my toe into the murky waters of VBA, so here is
a short macro to write your list of A1&A2&....A256 into the active cell:

Sub A1ANDA2()
myString = "A1"
For i = 2 To 256
myString = myString & "&A" & i
ActiveCell.FormulaR1C1 = myString
End Sub


Don Guillett replied on 16-Oct-08 02:34 PM
Just dipping your toe. RE read the post to see that OP wanted the contents
of each cell a1:256. So, here are some mods to your code

Sub A1ANDA2()
'mystring = "A1"
For i = 2 To Cells(Rows.Count, "a").End(xlUp).Row '256
'mystring = mystring & "&A" & i
mystring = mystring & Cells(i, "A")
'mystring = mystring & " " & Cells(i, "A")
MsgBox mystring
ActiveCell = mystring
'ActiveCell.FormulaR1C1 = myString
End Sub

Don Guillett
Microsoft MVP Excel
SalesAid Software
ShaneDevenshir replied on 17-Oct-08 02:21 AM
Hi Jason,

what you seem to be asking for and what your formula seems to want to don't
look the same to me.

You can get the last numberical value in a row or column (the contents of
the last cell) by using the old standards:

=LOOKUP(1E+99,B:B)   This is for a column

or the last text entry, which can be found using:

=LOOKUP(REPT("z",99),A:A)    This is for a column

You can modify both of these to work for rows.

Most often your text entrys are short so you can repeat the z the maximum
number of times which your text could be long.  (well that reads well!!)  The
reason to use 255 is that that is the largest value REPT will accept. For the
numeric equivalent you could enter 1E307, which is a really big number and
very close to the maximum value Excel will understand.

Now I may offend someone when I say I first introduced the REPT idea about
20 years ago in an PC Magazine or PC World article.  I could look it up if
anyone cares.

Shane Devenshire
Thomas Hardy replied on 18-Oct-08 03:26 PM
Thanks Don,

I reread the post and recognise that there is some ambiguity in the
question. The subject mentions the last item in a column of cells, the
concatenation mentions A1:A256 yet Jason also mentions the 'last column'. In
any event you have correctly identified a lack of attention to detail in my
original scan through the question so thanks for that.

That aside, could you explain the elements of the following item in your
code and what it is expected to return? I have run your code and can see the
overall result but am not sure precisely how this step works:

Cells(Rows.Count, "a").End(xlUp).Row '256

Thanks very much.

Ron Rosenfeld replied on 18-Oct-08 04:02 PM
Last non-blank cell in column A:


For A:A, if you are using XL2003 or earlier, you may have to use a reference
not larger than A1:A65535.
Don Guillett replied on 18-Oct-08 04:27 PM
Mine was for ROWS. For columns try this

Sub A1ANDA2()
For i = 2 To Cells(1,columns.count).End(xlToLeft).column '256
mystring = mystring & Cells("a", i)
'mystring = mystring & " " & Cells(1, i)
'  MsgBox mystring
ActiveCell = mystring
End Sub

But, to answer your question.
Cells(Rows.Count, "a").End(xlUp).Row
Cells(Rows.Count, 1).End(xlUp).Row
is counting the rows in col a and getting the last row. Look in the help
index for cells.

Don Guillett
Microsoft MVP Excel
SalesAid Software