Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Contents of 3 Columns into 1
Hello I've got a "small" problem that has become HUGE I have data in 3 columns for example D E F, what i wan't to do is to move the contents of D E F into D and remove any spaces in between and do so for 10k rows or more. No problem to do manually for a few but quite hefty for 10k data posts. Someone help me writing a macro, because i'm stuck. -- Lynxen ------------------------------------------------------------------------ Lynxen's Profile: http://www.excelforum.com/member.php...o&userid=25164 View this thread: http://www.excelforum.com/showthread...hreadid=386612 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Contents of 3 Columns into 1
This works in Excel 2000.
The Trim() removes leading and trailing spaces If you are working with values than remove Trim [You can use Cells(), Cells().Value, Cells().Text] =========================== Dim rw as Long, x as Long rw = Cells(Rows.COUNT, "C").End(xlUp) For x = 1 to rw Cells(x, 4) = Trim(Cells(x, 4)) & Trim(Cells(x, 5)) & Trim(Cells(x, 6)) Next ============================== -- steveB Remove "AYN" from email to respond "Lynxen" wrote in message ... Hello I've got a "small" problem that has become HUGE I have data in 3 columns for example D E F, what i wan't to do is to move the contents of D E F into D and remove any spaces in between and do so for 10k rows or more. No problem to do manually for a few but quite hefty for 10k data posts. Someone help me writing a macro, because i'm stuck. -- Lynxen ------------------------------------------------------------------------ Lynxen's Profile: http://www.excelforum.com/member.php...o&userid=25164 View this thread: http://www.excelforum.com/showthread...hreadid=386612 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Contents of 3 Columns into 1
Try this. Sub consolidate() dim totRow, a as integer totRow = ActiveSheet.UsedRange.Rows.Count For a = 1 to totRow Cells(a,4).Value = Trim(Cells(a,4).Value) & " " Trim(Cells(a,5).Value) & " " & Trim(Cells(a,6).Value) Next end su -- prepotenc ----------------------------------------------------------------------- prepotency's Profile: http://www.excelforum.com/member.php...fo&userid=2415 View this thread: http://www.excelforum.com/showthread.php?threadid=38661 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Contents of 3 Columns into 1
I assume you meant in code?
Public Sub CombineColumns() Dim rngCurrent As Range Dim wksCurrent As Worksheet Set wksCurrent = Sheets("Sheet1") Set rngCurrent = wksCurrent.Range("D65536").End(xlUp) Do While rngCurrent.Row 1 rngCurrent.Value = Trim(rngCurrent.Value) & _ Trim(rngCurrent.Offset(0, 1).Value) & _ Trim(rngCurrent.Offset(0, 2).Value) rngCurrent.Offset(0, 1).Value = "" rngCurrent.Offset(0, 2).Value = "" Set rngCurrent = rngCurrent.Offset(-1, 0) Loop End Sub -- HTH... Jim Thomlinson "Lynxen" wrote: Hello I've got a "small" problem that has become HUGE I have data in 3 columns for example D E F, what i wan't to do is to move the contents of D E F into D and remove any spaces in between and do so for 10k rows or more. No problem to do manually for a few but quite hefty for 10k data posts. Someone help me writing a macro, because i'm stuck. -- Lynxen ------------------------------------------------------------------------ Lynxen's Profile: http://www.excelforum.com/member.php...o&userid=25164 View this thread: http://www.excelforum.com/showthread...hreadid=386612 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Contents of 3 Columns into 1
Thanks for the help, the first method worked grea -- Lynxe ----------------------------------------------------------------------- Lynxen's Profile: http://www.excelforum.com/member.php...fo&userid=2516 View this thread: http://www.excelforum.com/showthread.php?threadid=38661 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I combine the contents of two columns? | Excel Worksheet Functions | |||
combined contents in two different columns | Excel Discussion (Misc queries) | |||
Counting the Contents of Two Columns | Excel Discussion (Misc queries) | |||
How do I merge the contents of two columns? | Excel Discussion (Misc queries) | |||
Macro to copy cell contents number of columns | Excel Programming |