Macro to select last cell with text when there are formulas in the cells

[Silverhorne]

Good Day!
I have been looking for a solution for this but have haven't been able to find the answer anywhere, my last resort is to post here, hopefully someone can help.

I am trying to create a macro that copies certain cells (C2 to C?) to the clipboard, these cells are populated depending on the number of tests completed to the item.
The cells in C2 to C7 are always populated but cell C5 is always blank.
I would like to copy the cells where the formula displays text, the dynamic cells will be C8 to C14.
The macro below finds the last row with formulas .End(xlUp) this is not what I want.

Sub SELECT_FOR_MAPPING()

Lrow = Range("C" & Rows.Count).End(xlUp).Row
Range("C2:C" & Lrow).Copy

End Sub

It important to know that the formulas in cells C9 to C14 have a formula that will leave a space in the cell, so they may appear to have text when it is just a space.
I have created a numbered list in B2 to B14 to use as a reference for a count function but I am not sure how to string the code together as am pretty new to the code part of excel.
Sorry, I can't get Xl2bb to do a mini sheet, only a table...I think it might have to do with the large formulas pulling the data into these cells.
Thanks in advance for any help.

1	1	128-MS-1-24-4		128-MS-1-24-4		11
	2	CCI-ST-8
	3	18/Feb/2023
	4
	5	LH LH C14 C14
	6	CR CR PG PG
	7	MD 24"/12.7 mm
	8	PH: test1
	9	RT: 20230218 RT TLH-01
	10	RT1: test1
	11	RT2: test1

 

[Anthony47]

Try

LRow = Evaluate("MAX(IF(C1:C10000<>"""",ROW(C1:C10000),""""))")

 

[Silverhorne]

That is still copying all cells from C2 to C14.
Here is the macro I tried along with the formula that is in cells C8 to C14.

Sub SELECT_FOR_MAPPING()

lRow = Evaluate("MAX(IF(C1:C10000<>"""",ROW(C1:C10000),""""))")
Range("C2:C" & lRow).Copy

End Sub

=(IF(ISNA(INDEX(Hidden!$G$3:$G$13,MATCH(1,Hidden!$F$3:$F$13,FALSE),1)),"",INDEX(Hidden!$G$3:$G$13,MATCH(1,Hidden!$F$3:$F$13,FALSE),1))&" "&IF(ISNA(INDEX(Hidden!$H$3:$H$13,MATCH(1,Hidden!$F$3:$F$13,FALSE),1)),"",INDEX(Hidden!$H$3:$H$13,MATCH(1,Hidden!$F$3:$F$13,FALSE),1)))

This does an index match, adds a space, and does another index match.

Thank you for your answer.

 

[Anthony47]

Could you attach an XL2BB minisheet where formulas and cell coordinates are visible?

 

[Silverhorne]

I've tried to use the mini sheet option on both of my computers and on different ranges of cells with formulas and it keeps crashing excel.
I looking at the help forum for XL2BB to see if I can make it work....

 

[Silverhorne]

Just for reference, the top left cell pictured in the table above is A1.

 

[Silverhorne]

I tried my best to duplicate what I have in the original sheet, if the solution works on this, it should work on the original sheet.

Select Copy Macro.xlsm
	B	C	D	E	F
2	1	128-MS-1-24-4		1	128-MS-1-24-4
3	2	CCI-ST-8		2	CCI-ST-8
4	3	18/Feb/2023		3	18/Feb/2023
5	4			4
6	5	LH LH C14 C14		5	LH LH C14 C14
7	6	CR CR PG PG		6	CR CR PG PG
8	7	MD 24"/12.7 mm		7	MD 24"/12.7 mm
9	8	PH: test1		8	PH: test1
10	9	RT: 20230218 RT TLH-01		9	RT: 20230218 RT TLH-01
11	10	RT1: test1		10	RT1: test1
12	11	RT2: test1		11	RT2: test1
13				12
14				13
Sheet1

Cell Formulas
Range	Formula
C2:C4,C6:C14	C2	=IF(F2=""," ",F2)
B9:B14	B9	=IF(C9=" ","",B8+1)

 

[Anthony47]

I missed that the formula returns a "Space"
Try

LRow = Evaluate("MAX(IF(len(C1:C10000)>1,ROW(C1:C10000),""""))")

This assumes that a string longer than 1 character is returned by the formula, or one Space; could it work?

 

[Silverhorne]

Thank you very much, that worked perfectly!

 

[Anthony47]

It took longer than necessary but we made it!