Convert vba to formula with Named Ranges

[detriez]

I need to convert this to a formula
If a cell is not blank, concatenate the Header (nMerge1, nMerge2, nMerge3 etc) and the cell value
I use a named range for the header because the header will always change.

The challenge is, columns may be inserted or deleted which can change the column reference.. Would a named range be applicable here too?
Not every cell in the column will be populated so I cant use End(xlUP).Row

lr = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1

    Application.ScreenUpdating = False
    With Range("AL2:AL" & lr)
        .FormulaR1C1 = "=IF(RC[1]<>"""",CONCATENATE((R1C39&"": ""),RC[1])&CHAR(10),"""")&" & _
                       "IF(RC[2]<>"""",CONCATENATE((R1C40&"": ""),RC[2])&CHAR(10),"""")&" & _
                       "IF(RC[3]<>"""",CONCATENATE((R1C41&"": ""),RC[3])&CHAR(10),"""")&" & _
                       "IF(RC[4]<>"""",CONCATENATE((R1C42&"": ""),RC[4])&CHAR(10),"""")

My formula.. Should this be AND or OR?

=IF(AN2<>"",nMerge1&": "&AN2,"")&CHAR(10)
IF(AN2<>"",nMerge2&": "&Ap2,"")&CHAR(10)
IF(AN2<>"",nMerge3&": "&AP2,"")&CHAR(10)
IF(AN2<>"",nMerge4&": "&AQ2,"")&CHAR(10)

This should be the end result

 

[Zot]

You just find last row like this. No need to specify which column.

Sub test()

Dim Last_Row As Long
   
On Error Resume Next
Last_Row = Cells.Find(What:="*", _
                After:=Range("A1"), _
                LookIn:=xlFormulas, _
                LookAt:=xlPart, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row
On Error GoTo 0
   
Debug.Print Last_Row

End Sub

 

[detriez]

Thanks Zot,
I'm sorry I wasn't more clear.
I cant use VBA as this will be an Excel 365 online sheet.
I need to do this via formulas

BTW...
I do like the way you find the last column.. I can use that part for other sheets

 

[Zot]

I actually do not really understand what you really want but only want to find last row using excel formula
The formula I found is using MATCH

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	1	1	1		1	1
2	2	2	2		2	2
3	3	3	3		3	3
4	4	4	4		4	4
5	5	5	5		5	5
6	6	6	6		6	6
7	7	7	7		7
8	8	8			8
9	9	9			9
10	10				10
11	11				11
12	12				12
13					13
14					14			12	9	7		14	6
15								14
Sheet1

Cell Formulas
Range	Formula
H14:M14	I14	=IFNA(MATCH(2,1/(B:B<>""),1),"")
H15	H15	=MAX(H14:M14)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[detriez]

I think I really wrote a confusing post with multiple questions.
Let me try again.

I need a formula that will concatenate the column header (row 1) which I have set up as a named range (nMerge1, nMerge2, nMerge3 etc) and the cell value if the cell value is not blank and add a carriage return line feed after each one.

How can I string this formula together?

=IF(AN2<>"",nMerge1&": "&AN2,"")&CHAR(10)
IF(Ao2<>"",nMerge2&": "&Ao2,"")&CHAR(10)
IF(AP2<>"",nMerge3&": "&AP2,"")&CHAR(10)
IF(AQ2<>"",nMerge4&": "&AQ2,"")&CHAR(10)

 

[Fluff]

Not sure where the named ranges come in as you are looking at individual cells, but maybe

=TEXTJOIN(CHAR(10),,AN2:AQ2)

 

[detriez]

Thanks Fluff
I am using Named Ranges for the "header cells" in row 1 because the user may insert/delete columns which could mess up the references to the headers..
Is there a better way to handle this?

 

[Fluff]

If you are using named ranges why are you also using cell references?

 

[detriez]

Good question. The end result I'm looking achieve is:

IF a cell is not blank, concatenate inot AM the Header and the cell value so, something like this:

 

[Fluff]

How about

+Fluff 1.xlsm
	M	N	O	P	Q
1		ToMerge 1	ToMerge 2	ToMerge 3	ToMerge 4
2	ToMerge 1: abc ToMerge 3: xyz	abc		xyz
3	ToMerge 2: fgh ToMerge 4: klm		fgh		klm
Main

Cell Formulas
Range	Formula
M2:M3	M2	=TEXTJOIN(CHAR(10),,FILTER($N$1:$Q$1&": "&N2:Q2,N2:Q2<>"",""))