Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate values in an unlimited range of cells in a column WITHOUTalso calculating values that had previously been filtered AGAINST? | Excel Discussion (Misc queries) | |||
Excel 2007 doesnt show Y-axis values when the values are small. | Charts and Charting in Excel | |||
Exclude #N/A values and Return Numeric values to consecutive cells in Single Row | Excel Worksheet Functions | |||
Finding Most Recent Values in Col1 -- Summing Matching Values | Excel Discussion (Misc queries) | |||
Excel Compare values in columns & display missing values in a new | Excel Discussion (Misc queries) |