Always select the last 10 rows in excel 8

[Tadynn]

Hi all,

I assume this would be an easy one to answer......

Everytime I add a new line of data to a spreadsheet called "ORDP_Data", I want to click a button that will always select the last 10 lines added in colmun "F" and paste the rows to another spreadsheet in the same workbook.

Example: I click the button and fields F2:F12 are copied to another spreadsheet. If I add a new line of data in F13 and click the button again, F3:F13 will be copied and pasted, and so on......

I have created I think the first part of it by using xlDown

Sub MATSelection()

Selection.End(xlDown).Select

End Sub

Thanks in advance

Tadynn

 

[tahilg]

I haven't done this in VBA but I have done something similar using =COUNTA(F:F). This would go in cell E1 say. You can adapt this to your circumstances.

HTH

Gunny

 

[xlbo]

This should work - will take the last 10 entries from col F on the active sheet and paste to A1 on sheet2

Sub MATSelection()
dim lRow as long
lRow = cells(65536,6).end(xlup).row
range("F" & lRow & ":F" & lRow -10).copy destination:=sheets("Sheet2&quot.range("A1&quot
End Sub


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[KenWright]

Assuming your data is on Sheet1 starting Col A and that you want a 10 x 4 array of the last 10 rows by 4 cols returned to a 10 x 4 array on another sheet. On the other sheet, select the 10 x 4 array that is to hold the data, paste in the following formula and then array enter it using CTRL+SHIFT+ENTER:-

=OFFSET(Sheet1!A1,MAX((NOT(ISBLANK(Sheet1!A1:A1000)))*ROW(Sheet1!A1:A1000))-10,,10,4)

I have limited it to 1000 rows but you may want increase that. Try to keep your array as small as possible yet encompassing the max you think it will go to.

As you add data to sheet1 it will automatically change the data on your other sheet to have just the last 10 rows of data.

Regards
Ken...............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[chrisaroundtown]

KenWright!!!!!!

This worked, thanks. But I have no idea why.

Can you please explain what an array formula is and how it changes a standard formula?

Thanks
Chris

 

[Tadynn]

Thanks Xlbo, Kenwright for your input.....

For the question asked, xlbo's answer was more along the lines of what I am trying to do in terms of vba coding. However, being a novice excel user, I never thought of how it could be done via a formula and am going to try it out Kenwright's tomorrow at work as it may be a more reasonable approach..
So thanks for that.

Rgrds, tadynn

 

[KenWright]

Oh boy - Explaining arrays could take till Xmas

Anyway, to try and net this out, the formula does a couple of things,

=ISBLANK(Sheet1!A1:A1000)*ROW(Sheet1!A1:A1000)

sets up a series of TRUE/FALSE answers to both statements, the first being ISBLANK(Sheet1!A1:A1000)

which for each cell in the range A1:A1000 will give you (In Excel's memory only - not on screen) a series of answers such as:-

A
1 TRUE
2 TRUE
3 FALSE
4 FALSE
5 FALSE
6 FALSE
7 FALSE
8 TRUE
9 TRUE

where TRUE denotes that the cell is blank and False denotes that it is not. The NOT(ISBLANK.... bit however, reverses that, so that you get:-

A
1 FALSE
2 FALSE
3 TRUE
4 TRUE
5 TRUE
6 TRUE
7 TRUE
8 FALSE
9 FALSE

The ROW(Sheet1!A1:A1000) gives you a series of numbers denoting the Row number of that row, so you get:-

A
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9

In Excel the value TRUE is equal to 1 and FALSE is equal to 0, so what you get from the formula

=NOT(ISBLANK(Sheet1!A1:A1000))*ROW(Sheet1!A1:A1000)

is the following:-

A
1 FALSE * 1 = 0 * 1 = 0
2 FALSE * 2 = 0 * 2 = 0
3 TRUE * 3 = 1 * 3 = 3
4 TRUE * 4 = 1 * 4 = 4
5 TRUE * 5 = 1 * 5 = 5
6 TRUE * 6 = 1 * 6 = 6
7 TRUE * 7 = 1 * 7 = 7
8 FALSE * 8 = 0 * 8 = 0
9 FALSE * 9 = 0 * 9 = 0

and the MAX bit simply gives you the highest number which is 7. This then tells you that row 7 in Col A is the last cell with any data in it.

The OFFSET bit

=OFFSET(Sheet1!A1,MAX((NOT(ISBLANK(Sheet1!A1:A1000)))*ROW(Sheet1!A1:A1000))-10,,10,4)

allows you to take a reference such as A1, and then move x number of rows away from it (in this case 7) depending on what number is returned by the array we just went through.

The OFFSET function is as follows:-

=OFFSET(reference,rows,cols,[height],[width]) with the last two parameters being optional. Gist of it being, start at A1 (reference), move down 7 rows, move over no columns (left blank), and then starting from that cell you can also specify a range of cells so many rows deep and so many columns wide.

the -10 in the actual formula simply says, after having found the last cell, go back 10 rows, then from that cell give me a range that is 10 rows deep and 4 columns wide.

Don't know if this helps, but hopefully it does somewhat.


It might be easier if I give you another example of a formula that doesn't actually have to be entered as an array, but performs an array function. It's an oddity, but might make it easier to understand. the function in particular is called SUMPRODUCT:-

As I said, an array formula exploits the fact that Excel interprets TRUE as 1 and FALSE as 0. Take the formula below:-

=SUMPRODUCT((A9:A20=A1)*(B9:B20="A&quot*(C9:C20))

This sets up an array that gives you something that looks like this
(depending on the variables of course):-

A B C
9 TRUE * FALSE * 3
10 FALSE * FALSE * 4
11 TRUE * TRUE * 2
12 TRUE * TRUE * 1
13 TRUE * FALSE * 4
14 TRUE * TRUE * 3
15 TRUE * TRUE * 2
16 FALSE * TRUE * 8
17 TRUE * TRUE * 6
18 TRUE * TRUE * 8
19 TRUE * TRUE * 7
20 TRUE * TRUE * 6

Which because TRUE=1 and FALSE=0, is interpreted as:-

A B C
9 1 * 0 * 3 = 0
10 0 * 0 * 4 = 0
11 1 * 1 * 2 = 2
12 1 * 1 * 1 = 1
13 1 * 0 * 4 = 0
14 1 * 1 * 3 = 3
15 1 * 1 * 2 = 2
16 0 * 1 * 8 = 0
17 1 * 1 * 6 = 6
18 1 * 1 * 8 = 8
19 1 * 1 * 7 = 7
20 1 * 1 * 6 = 6
-------------
35

and the SUM bit just adds up all the end values of the products



If you exclude the last part of the formula, so that it becomes:-

=SUMPRODUCT((A9:A20=A1)*(B9:B20="A&quot)

then what you end up with is a sum of a bunch of TRUE/FALSE values depending on whether or not the criteria has been met on that row, and this is the same as counting the number of records that meet your criteria. Imagine the above tables without Column C, and the last one would look like the following:-

A B C
9 1 * 0 = 0
10 0 * 0 = 0
11 1 * 1 = 1
12 1 * 1 = 1
13 1 * 0 = 0
14 1 * 1 = 1
15 1 * 1 = 1
16 0 * 1 = 0
17 1 * 1 = 1
18 1 * 1 = 1
19 1 * 1 = 1
20 1 * 1 = 1
-------------
8

Chip Pearson has a good intro on his site here:-

http://cpearson.com/excel/array.htm

Regards
Ken...............





----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[xlbo]

Ken - whilst I know how array formulae work, this is a very comprehensive explanation and includes a useful bit on offset so......have a star

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[KenWright]

Why, You are too kind sir - many thanks

Cheers
Ken..............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[chrisaroundtown]

Thanks a lot Kenwright,

All makes sense, I'll have to experiment a bit with them.

Have another star.

Thanks
Chris

 

[tahilg]

How about:

=OFFSET(Sheet1!A1,COUNTA(A:A)-10,,10,4)

It does the same thing as Ken's formula but is (1) simpler and (2) not limited to a pre-specified number of rows.

Am I wrong?

Gunny

 

[KenWright]

The downside is that it will bomb if there are any blank cells in the data in Col A, hence the MAX element in the formula. You are quite correct in that it can be shortened if a number of rules are adhered to, but personally I hate limitations of any kind, so unless there was a significant impact as a result of the larger array formula, it is possibly slightly more flexible in that respect.

That having been said, it is indeed well worth considering if indeed the data does have no gaps, or you could perhaps cater for it by including a COUNTBLANK as well so that you definitely get the right number of cells.

Either way, Star from me for pointing it out though.

Regards
Ken...............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[tahilg]

Right Ken. I tried to post a follow-up to that effect but the website went off.

But there would be a similar problem with blank cells in your formula. The offset of -10 could land you in a blank cell.

COUNTBLANK would not really solve this problem.

I guess either way you need to stipulate "no blank cells".

And, thanks for the star. My very first - ever.

Gunny

 

[KenWright]

I'm taking it as a given that the OP is after the last 10 rows, whatever they may be, working backwards from the very last entry to be found in Col A.

Regards
Ken.................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[jdegeorge]

Ken

Your explanation of this formula is amazing! Thanks for the lesson on arrays. And, enjoy a star on me!

Jim DeGeorge

 

[luceze]

Ken,
I use array formulae all the time but never bothered to understand how they work. Great explanation! Star!!

Eric

 

[SkipVought]

Ken,

I had not taken the time to review your treatise. It is impressive, to say the least!

I am trying to get up-to-speed with the King of Arrays/Sumproduct, but while you are proceeding at warp speed, I am yet attempting to crawl to the Captain's Chair! ;-)

Have another well deserved STAR!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[tahilg]

Alright who done it? Who gave me my second star! How dare anyone give me a star without telling me!

Gunny