How to include last row function in an excel formula incorporated into a macro

HI everyone,

I am trying to create a macro, using couple of excel formula and record macro option in excel.


Column A contains repetitive value (i.e, "apple" mentioned 3 times, "Orange" mentioned 2 times down the row).
First objective is to find the unique value and copy paste it in column C.
Formula used in column C: =IFERROR(INDEX($A$2:$A$278, MATCH(0,COUNTIF($C$1:C1, $A$2:$A$278), 0)),"")


Column B contains respective individual value in regards to column A cell value.
Using concatenate function I wanted to combine column A and column B data separated by a comma.
Expected output data (i.e Apple (in column C): ABC, FGH, HJK (in column D).
Formula used in column D: =CONCATENATEIF($A$2:$A$278, C2, $B$2:$B$278, ",")

The number of rows in the column A and B is not always 278, is there any way to incorporate last used row# into the formula itself or is there any other option ? Thank you

Recorded Macro:

Please Login or Register  to view this content.

You could use such approach (shown on the example of your first subroutine):

Please Login or Register  to view this content.

One of my favourites, the Replace method.

Please Login or Register  to view this content.

I like the approach using Replace. Never seen that done before. That said, I've never liked using .Formula.R1C1 (or AutoFill).

I'd do it like this:

Please Login or Register  to view this content.

Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.