Excel – Automatically select last row in a set in Excel

To find earliest instance, mentioned below is the formula:-

=LOOKUP(2,1/(A:A=A2),ROW(A:A))

For your better understanding, below is the screenshot

Just as a warning to you and any other who attempt to do this:

Right now you should think about:

• how often will I use this per customer?
• how many customers are there?
• how will I input these Items?
• how many different Items are there?

These are the most important questions. I have done quite exactly, what you are trying to do, for a very small company, with aroud 3 receipts per month.

As always, please fast & cheap & excelOnly. However, nobody asked those questions above. Mainly because of cheap & excelOnly. And now, it is so obvious how much time is lost, whenever a receipt is searched, corrected, but mainly when the items are added. Not to mention how many different forms of the same item exists. Correcting this with excel is however possible, but IMHO, a real pain. Convinced the cutomer too, an EPR is now setup.

This is a database task. It is like one of the best examples of a database task!

Further, this might even be worth a small business ERP-System. Here in Germany, I would recommend you JTL-Wawi.

But, if this is too big of a jump, you should really think about using Access.

Some advantages:

• you can manage your customers
• you can manage your items
• you can move your dymanic behaviour to access
• you can manage your receipts easily
• you can still use excel as your output system

How this would work?

You would have some tables, for customers and items, and for receipts. The receipt table would connect a customer to his items and other receipt relevant attributes.

Then you could create a designed report for a basic receipt, and print it from access. Or use excel to select a receipt, get the data and print it. Because the data would be predetermined, you would know how many rows/cols are comming, and what their context is.

However - this wheel was already invented so many times, so you should look into the already existing ones ;)

Edit

A solution based on your behaviour request could use this attempt here:

https://stackoverflow.com/questions/12604274/excel-vba-is-there-a-textchanging-textchanged-or-a-similar-event-or-how-to/12604570#12604570

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Column = clngColumnRightToLastGrade Then
    Application.EnableEvents = False
    'offset selection, one row down, two cols to left
    Target.EntireRow.Insert (xlShiftDown) 'insert new row
    Target.Offset(1, -2).Select
    Application.EnableEvents = True
  End If
End Sub

You could of course use the Change event instead and you might have to limit this behaviour to the maximum range of your dynamic list or to the worksheet of it.

Or just make a macro acceible per shortkey to execute this line here:

Target.EntireRow.Insert (xlShiftDown) 'insert new row

You will need to determine the currently selected cell to have the Target range.