Copy Contents of 3 Columns into 1

[Lynxen]

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.

[prepotency]

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 sub

[STEVE BELL]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Jim Thomlinson]

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
>
>

[Lynxen]

Thanks for the help, the first method worked great