You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Finding Last Row Containing Value

Advertisement

QUESTION: Is there a way to find the last row a certain value occurred without resorting to VBA or arrays? (computer runs out of firepower before the whole list is searched :S)

Thanks!

ANSWER: I would have thought that a proper VBA function would be the least memory intensive solution, but the only Non VBA, non Array solution I can think of would involve 2 formulas to get the data

In my example, my data is in A1:a9, and I've put the first forumula in column C

=IF(COUNTIF(A1:$A$9,A1)=1,A1,"")

You will note I've mixed my references to column A - this is so that as the formula is copied down the sheet the range to test will reduce - so column C will ONLY contain the value of column A the last time it is found.

You can then use the match function to get the row that is on

=MATCH(5,$C$1:$C$9)

(in this case, I've hard coded the 5, but in reality it would presumably be another cell reference)

The array formula version (for completeness) would be =MAX(IF($A$1:$A$9=5,ROW($A$1:$A$9),0)) and the VBA method I would use would be a custom function

Public Function MaxVal(rar As Range, vl As Variant) As Long

Dim cl

For Each cl In rar

If cl.Value = vl Then MaxVal = cl.Row

Next

End Function

---------- FOLLOW-UP ----------

QUESTION: Thanks so much for helping out.

I would agree that VBA would be the better option but unfortunately, my workplace has disabled that option as SOE.

Re: Data in column A

How would we go about if the data goes across 6 columns?

Thanks again!!

My array method would still work, just with a wider range of data. For a non array, I think we'd need to use TWO helper columns - the first would check if any value in the row matched the value being searched, so

=if(countif(a1:f1,5)>0,5,"")

again hard coded 5, but this would be a cell reference I would guess. This would then give you a column that had the values, which you could then use the decreasing countif formula from yesterday with.

Hopefully this makes sense!

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.**Education/Credentials**

I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!