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

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

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

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

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