Find Last Cell greater than 0 in Column and Copy Range

[buzz71023]

Good morning all,
I thought this would be an easy research and implement but the few examples I have found, just didn't work.
I have data in range L1 to Q100, all with formula calculations.
I need to find the last cell in column "O" greater than zero and copy the range L1 to Q(last row greater than zero).

Thank you in advance for any time you invest in this and all help is greatly appreciated.

BT

 

[felixstraube]

Do you still have Excel 2016? Because with newer version the answer would change

 

[buzz71023]

Yes I still have 2016

 

[Fluff]

How about in Q1 copied down

=IF(ROW()=LOOKUP(2,1/($O$1:$O$100>0),ROW($O$1:$O$100)),L$1,"")

 

[felixstraube]

How about this?:

For this array formula to work in excel 2016 you will have to select from A1 to F100 (or any range with the same size as your data) then edit and past the formula and hit Ctrl+Shift+Enter.

Libro1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	66	72	31	65	35	34						66	72	31	65	35	34
2	48	70	68	64	47	94						48	70	68	64	47	94
3	74	9	31	50	43	5						74	9	31	50	43	5
4	37	83	99	37	63	70						37	83	99	37	63	70
5	71	18	40	10	66	58						71	18	40	10	66	58
6	32	98	35	38	10	66						32	98	35	38	10	66
7	75	26	4	57	33	57						75	26	4	57	33	57
8	14	36	40	41	83	40						14	36	40	41	83	40
9	56	80	59	34	22	26						56	80	59	34	22	26
10	69	85	35	34	40	77						69	85	35	34	40	77
11	47	73	1	66	9	37						47	73	1	66	9	37
12	52	29	22	34	33	19						52	29	22	34	33	19
13	97	39	83	73	88	91						97	39	83	73	88	91
14	85	63	74	5	89	26						85	63	74	5	89	26
15	19	18	38	8	97	89						19	18	38	8	97	89
16												40	37	27		47	3
17												23	8	19		16	91
18												30	30	63		4	34
19												1	15	79		56	49
20												75	60	22		24	45
21												76	18	90		75	4
22												65	37	88		56	7
23												10	51	2		100	97
24												7	63	76		21	9
25												25	7	4		4	39
26												33	68	38		18	48
27												10	93	28		18	56
28												67	77	89		53	55
29												41	38	2		24	11
30												26	92	33		25	41
31												12	36	95		1	67
32												84	50	6		56	6
33												75	50	41		80	46
34												93	33	41		98	39
35												11	56	22		20	25
36												62	58	57		63	24
37												64	68	15		83	91
38												2	44	65		21	51
39												57	92	73		55	1
40												38	11	68		40	8
41												40	81	35		17	20
42												51	93	24		67	26
43												50	77	27		1	93
44												60	32	77		64	11
45												11	77	25		15	24
46												6	63	5		59	28
47												93	71	1		40	79
48												100	11	47		34	28
49												40	83	69		17	48
50												95	13	52		19	6
51												48	1	41		53	33
52												28	76	42		2	70
53												38	87	73		49	81
54												54	25	36		80	17
55												18	45	26		85	49
56												27	57	13		67	3
57												34	88	12		4	39
58												63	77	41		74	96
59												65	41	36		100	54
60												87	41	75		95	46
61												74	39	44		38	21
62												26	21	46		84	56
63												63	45	65		77	75
64												43	66	74		24	42
65												67	90	99		73	85
66												60	64	100		30	85
67												12	18	14		7	98
68												42	97	96		11	93
69												2	92	50		22	24
70												40	99	6		71	17
71												87	42	70		93	100
72												80	96	38		13	92
73												72	62	55		98	76
74												66	50	17		93	80
75												68	91	41		61	95
76												93	21	99		56	5
77												96	18	90		4	56
78												32	17	84		78	8
79												51	40	20		28	18
80												81	11	33		11	34
81												81	79	15		96	69
82												73	31	27		63	93
83												98	56	64		46	28
84												54	99	50		47	82
85												86	37	92		29	5
86												99	64	36		65	89
87												65	2	49		59	5
88												93	27	70		98	55
89												42	79	12		66	18
90												1	30	76		84	11
91												52	64	13		81	11
92												92	68	21		25	17
93												45	51	38		14	75
94												97	41	65		88	84
95												69	25	21		97	66
96												43	50	80		84	62
97												71	92	38		93	20
98												93	26	26		13	2
99												1	57	52		37	52
100												3	5	36		33	65
Hoja1

Cell Formulas
Range	Formula
A1:F15	A1	=OFFSET($L$1,0,0,MAX(ISNUMBER($O$1:$O$100)*ROW($O$1:$O$100)),6)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[buzz71023]

How about in Q1 copied down

=IF(ROW()=LOOKUP(2,1/($O$1:$O$100>0),ROW($O$1:$O$100)),L$1,"")

Apologies, I probably didn't specify the "copy" function I am needing.
I am going to have to copy the whole range of data (L1 to Q last greater than zero row) and paste it into another database.
Wont that have to be a VBA function.

 

[Fluff]

Wont that have to be a VBA function.

Yes it will.

 

[buzz71023]

There is probably a much better way to do this but after a couple more hours of research this is what I came up with.
I appreciate your input on the formulas above.

Dim LastRNG As Range, StartRNG As Range, CopyRNG As Range

    Dim B As Long, BB As Long
    BB = Cells(Rows.Count, "O").End(xlUp).Row
    For B = BB To 1 Step -1
        If Cells(B, 1).Value = 0 Then
            Cells(B, 1).Offset(-1, 16).Select
        End If
    Next

Set LastRNG = ActiveCell
Set StartRNG = Range("L1")
Set CopyRNG = Range(StartRNG, LastRNG)

CopyRNG.Copy

 

[Fluff]

Glad you sorted it & thanks for letting us know.

 

[Peter_SSs]

There is probably a much better way to do this ..

If you wanted to investigate that could you clarify the requirement?

Post #1 said

I need to find the last cell in column "O" greater than zero

Code in post #8 finds last cell in column A greater than zero.

Could we see some sample data that includes column A down past the last non-zero value? (You could hide columns B:K)