Getting the Last 4 values in a row?
 

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

Getting the Last 4 values in a row?
 

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" wrote in message
...
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

Getting the Last 4 values in a row?
 

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" wrote:

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" wrote in message
...
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

.

Getting the Last 4 values in a row?
 

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

.

Getting the Last 4 values in a row?
 

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



"Otto Moehrbach" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

.

.

Getting the Last 4 values in a row?
 

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



"Themd" wrote in message
...
This worked perfectly! Thank you very much. I wish there were online
classes that could assist VBA challenged users like me:)



"Otto Moehrbach" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

.

.