Getting the Last 4 values in a row?

[Themd]

Hi all,

I have a sheet with data consisting of 8 columns and 5 rows.

A B C D E F G H
1
2
3
4
5
Column A contains names and the remaining columns and the remaining columns
contain data as such;
1 2 3 4 5 6 7 8 9
A v1 v2 v3 v4 v5
B v1 v2 v3
C v1 v2 v3 v4
D v1 v2 v3 v4 v5
E v1 v2 v3 v4 v5

What I need to do is to get the last 4 value in each column. How can I do
that.

Thanks for your help,
Themd

 

[Otto Moehrbach]

That's easy enough to do, but what do you want to do with them? You say you
want to "get" them. What do you mean? Do you want them placed somewhere?
Where? Do you want to simply see them in a message box. What? HTH Otto

 

[Themd]

Hey sorry,

Yes, if possible I would like to delete all the columns except the last 4 in
each row. So I will be left with:

1 2 3 4
A
B
C
D

I would like to remind that some of my rows have blanks in some columns, so
I need to have the last 4 numerical value.

Thanks,
Themd

 

[Otto Moehrbach]

This macro will do what you want. I assumed that your first row contains
headers and that your data starts in row 2. As written, this macro will
work with any number of rows. Come back if you need more. Otto
Sub GetLast4()
Dim rColA As Range, rRow1 As Range
Dim rRowi As Range, i As Range
Dim c As Long, HowMany As Long
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set rRow1 = Range("B1:H1")
For Each i In rColA
Set rRowi = rRow1.Offset(i.Row - 1)
For c = rRowi.Count To 1 Step -1
If IsEmpty(rRowi(c)) Then _
rRowi(c).Delete Shift:=xlToLeft
Next c
HowMany = Application.CountA(rRowi)
If HowMany > 4 Then
i.Offset(, 1).Resize(, HowMany - 4).Delete Shift:=xlToLeft
End If
Next i
End Sub

 

[Themd]

This worked perfectly! Thank you very much. I wish there were online
classes that could assist VBA challenged users like me

 

[Otto Moehrbach]

Glad it worked for you. Thanks for the feedback. Here are some sites that
might help. Otto
Excel References
John Walkenbach's http://www.j-walk.com/ss
Stephen Bullen's: http://www.bmsltd.co.uk
Baarns Group (archive site): http://archive.baarns.com
Rob Bovey's AppsPro http://www.appspro.com
Frank Isaacs': http://www.vbapro.com
Chip Pearson: http://www.cpearson.com
http://www.i1.net/~alanb/xl_links.htm
http://ourworld.compuserve.com/homepages/BOBF/
http://www.j-walk.com/ss/sshelp.htm
http://www.microsoft.com/msexcel/
http://www.vex.net/~negandhi/excel/
http://www.cpearson.com/excel/CFColors.htm (Conditional Formatting Stuff)
http://www.decisionmodels.com/optspeed.htm (Computer is slow)

John Walkenbach's site has a very comprehensive list of links, so you can
find many
other sites from there.

A new reference tool:
http://www.otsiweb.com/howdi.htm
http://www.mvps.org/dmcritchie/excel/getstarted.htm
This last is a good tutorial in VBA for starters.

http://www.cpearson.com/excel/vbe.htm
This last is for doing things to code with code.

http://www.cpearson.com/excel/weeknum.htm

http://www.cpearson.com/excel/colors.htm

http://www.ozgrid.com/VBA/Sum.htm
http://www.ozgrid.com/VBA/Sort.htm
The above deal with colors also.

http://cpearson.com/excel/holidays.htm#Easter