Macro to select the last specific value in a column

[jwpfeiffer]

I've developed an action tracker where the status of each item is calculated as "R" for items past their due date, "A" for those still within date and "" if completed. This column is sorted Z to A on workbook open so that "R" items appear at the top, followed by "A" items, and "" items last.

To simplify the process of adding a new action to the list I've created an macro assigned button, but I need to be able to find the last "A" value in column J, so that a new action row can be inserted underneath. I'm currently locating the last value with a cell formula, to which my macro refers, but due to the complexity of the workbook, this calculation takes time and the macro runs slowly.

Could someone help me with the macro code to find the last "A" value in a sorted column and to insert a row underneath?

Thanks,
Justin

[GC Excel]

Hi and welcome to the forum,

Try this one line of code :

Please Login or Register  to view this content.

[MarvinP]

Hi Justin and welcome to the forum,

I like to know exactly what is happening so I write code I can read. Here is a UDF (User Defined Function) to return the Row number of the Last R in Column J. See if this code helps you learn VBA.

Please Login or Register  to view this content.

[jwpfeiffer]

Hi - thanks for your response. The code you sent is inserting a row after the last cell in the column containing my formula below, which calculates as a "" value, rather than "A":

=IF(OR(AND(I4>=TODAY(),K4=""),I4=""),"A",IF(ISNUMBER(K4),"","R"

Is the code finding the "A" in the formula rather than a calculated value?

[GC Excel]

Try this instead. The code I provided was for values in column A

Please Login or Register  to view this content.

If it doesn't work, please post sample workbook.