copy last 5 populated cells

[redspanna]

Can I please have help to get code to carry out simple task below

Find last populated cell through Column A of Sheet1

From that cell select the next 5 cells UP for both columns A and B and copy that data into cell D1

So if A50 is the last populated cell, the code would copy A45:B50 into D1

Many thanks in advance

 

[AliGW]

Are you asking for the contents of D1 to be a concatenation of those five cells? Are their contents text or numbers?

 

[redspanna]

Nope, just want the copied data pasted using D1 as the start point, so in example above A45:B50 would be copied and pasted into D1:E5
Yes, contents and text and numbers

thanks

 

[AliGW]

OK - does the list start in cell A1 or another cell? Will there be blanks in that range?

 

[redspanna]

ADVERTISEMENT

Yes always starts in A1 and NO blanks - I'm making this too easy for you

 

[AliGW]

Yes, you are! If these are numbers, try this:

Excel 2016 (Windows) 32 bit
	A	B	C	D	E
1	100	100		87	100
2	87	87		70	87
3	90	90		85	90
4	85	85		69	85
5	100	100		90	100
6	90	90
7	100	100
8	87	100
9	70	87
10	85	90
11	69	85
12	90	100
Sheet2

Cell Formulas
Range	Formula
D1	=INDEX(A:A,COUNT(A:A)-4,1)
D2	=INDEX(A:A,COUNT(A:A)-3,1)
D3	=INDEX(A:A,COUNT(A:A)-2,1)
D4	=INDEX(A:A,COUNT(A:A)-1,1)
D5	=INDEX(A:A,COUNT(A:A),1)
E1	=INDEX(B:B,COUNT(B:B)-4,1)
E2	=INDEX(B:B,COUNT(B:B)-3,1)
E3	=INDEX(B:B,COUNT(B:B)-2,1)
E4	=INDEX(B:B,COUNT(B:B)-1,1)
E5	=INDEX(B:B,COUNT(B:B),1)

 

[redspanna]

ADVERTISEMENT

Ok, As Column A will be mixture of text and numbers, example Apple 123 the formula

=INDEX(A:A,COUNT(A:A)-4,1)

gives a #VALUE! error

However column B holds numbers only so the formula

=INDEX(B:B,COUNT(B:B)-4,1)

works fine

 

[AliGW]

Hang on a mo.

 

[AliGW]

Try this (adapted for each cell in D):

=INDEX(A:A,COUNTA(A:A)-4,1)

 

[redspanna]

ahh the sneaky COUNTA eh ?

Many thanks- that's great

Have a great new year