Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with some code that Martin wrote
Martin wrote this code a while back and I'm trying to manipulate it so
that it returns the highest value in column 4 only, not a concatenation of all the values in column 4. Basically the code does this: Input: A B C D Europe Germany Name1 Frankfurt Europe Germany Name1 Munich Europe France Name2 Paris Asia Japan Name3 Tokyo Asia Japan Name3 Osaka Output: A B C D Europe Germany Name1 Frankfurt, Munich Europe France Name2 Paris Asia Japan Name3 Tokyo, Osaka Here's the code: Option Explicit Sub concat() Dim lRowFirst As Long Dim lRowLast As Long Dim lRow As Long lRowFirst = ActiveCell.CurrentRegion.Row + 1 lRowLast = ActiveCell.CurrentRegion.Rows.Count + lRowFirst - 2 For lRow = lRowLast To lRowFirst Step -1 If Cells(lRow, 1) = Cells(lRow - 1, 1) And _ Cells(lRow, 2) = Cells(lRow - 1, 2) And _ Cells(lRow, 3) = Cells(lRow - 1, 3) Then Cells(lRow - 1, 4) = Cells(lRow - 1, 4) _ & ", " & Cells(lRow, 4) Rows(lRow).Delete End If Next lRow End Sub Thank You, -- Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with some code that Martin wrote
Sub concat()
Dim lRowFirst As Long Dim lRowLast As Long Dim lLastCol As Long Dim lRow As Long lRowFirst = ActiveCell.CurrentRegion.Row + 1 lRowLast = ActiveCell.CurrentRegion.Rows.Count + lRowFirst - 2 For lRow = lRowLast To lRowFirst Step -1 If Cells(lRow, 1) = Cells(lRow - 1, 1) And _ Cells(lRow, 2) = Cells(lRow - 1, 2) And _ Cells(lRow, 3) = Cells(lRow - 1, 3) Then lLastCol = Cells(lRow, Columns.Count).End(xlToLeft).Column Cells(lRow, 4).Resize(, lLastCol - 3).Copy Cells(lRow - 1, 5) Rows(lRow).Delete End If Next lRow End Sub -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Dan R." wrote in message ps.com... Martin wrote this code a while back and I'm trying to manipulate it so that it returns the highest value in column 4 only, not a concatenation of all the values in column 4. Basically the code does this: Input: A B C D Europe Germany Name1 Frankfurt Europe Germany Name1 Munich Europe France Name2 Paris Asia Japan Name3 Tokyo Asia Japan Name3 Osaka Output: A B C D Europe Germany Name1 Frankfurt, Munich Europe France Name2 Paris Asia Japan Name3 Tokyo, Osaka Here's the code: Option Explicit Sub concat() Dim lRowFirst As Long Dim lRowLast As Long Dim lRow As Long lRowFirst = ActiveCell.CurrentRegion.Row + 1 lRowLast = ActiveCell.CurrentRegion.Rows.Count + lRowFirst - 2 For lRow = lRowLast To lRowFirst Step -1 If Cells(lRow, 1) = Cells(lRow - 1, 1) And _ Cells(lRow, 2) = Cells(lRow - 1, 2) And _ Cells(lRow, 3) = Cells(lRow - 1, 3) Then Cells(lRow - 1, 4) = Cells(lRow - 1, 4) _ & ", " & Cells(lRow, 4) Rows(lRow).Delete End If Next lRow End Sub Thank You, -- Dan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with some code that Martin wrote
Perfect, thanks Bob.
Bob Phillips wrote: Sub concat() Dim lRowFirst As Long Dim lRowLast As Long Dim lLastCol As Long Dim lRow As Long lRowFirst = ActiveCell.CurrentRegion.Row + 1 lRowLast = ActiveCell.CurrentRegion.Rows.Count + lRowFirst - 2 For lRow = lRowLast To lRowFirst Step -1 If Cells(lRow, 1) = Cells(lRow - 1, 1) And _ Cells(lRow, 2) = Cells(lRow - 1, 2) And _ Cells(lRow, 3) = Cells(lRow - 1, 3) Then lLastCol = Cells(lRow, Columns.Count).End(xlToLeft).Column Cells(lRow, 4).Resize(, lLastCol - 3).Copy Cells(lRow - 1, 5) Rows(lRow).Delete End If Next lRow End Sub -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Dan R." wrote in message ps.com... Martin wrote this code a while back and I'm trying to manipulate it so that it returns the highest value in column 4 only, not a concatenation of all the values in column 4. Basically the code does this: Input: A B C D Europe Germany Name1 Frankfurt Europe Germany Name1 Munich Europe France Name2 Paris Asia Japan Name3 Tokyo Asia Japan Name3 Osaka Output: A B C D Europe Germany Name1 Frankfurt, Munich Europe France Name2 Paris Asia Japan Name3 Tokyo, Osaka Here's the code: Option Explicit Sub concat() Dim lRowFirst As Long Dim lRowLast As Long Dim lRow As Long lRowFirst = ActiveCell.CurrentRegion.Row + 1 lRowLast = ActiveCell.CurrentRegion.Rows.Count + lRowFirst - 2 For lRow = lRowLast To lRowFirst Step -1 If Cells(lRow, 1) = Cells(lRow - 1, 1) And _ Cells(lRow, 2) = Cells(lRow - 1, 2) And _ Cells(lRow, 3) = Cells(lRow - 1, 3) Then Cells(lRow - 1, 4) = Cells(lRow - 1, 4) _ & ", " & Cells(lRow, 4) Rows(lRow).Delete End If Next lRow End Sub Thank You, -- Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To: Martin vov Gagern Speed uUp Excel 2007 Startup | Excel Discussion (Misc queries) | |||
who origonally wrote the workbook? | Excel Programming | |||
How do I find out what time I wrote A letter? | Excel Discussion (Misc queries) | |||
Miss-wrote previous "IF" problem | Excel Worksheet Functions | |||
My (old) function that I wrote doesn't work anymore! | Excel Programming |