Macro to move the contents of a cell

[JenBasch]

I am trying to figure out how to write a macro to take the contents of cells which are across a few columns and move them all into one long column.

Example:

|column1| |column2| |column3|
|dog.......| |bird.......| |cat.........|
|car........| |truck.....| |bus........|

Into

|column4|
dog
bird
cat
car
truck
bus

It seems like it shouldn't be too hard, I am very new to Macros, so any help would be greatly appreciated.

Thanks,
Jen

[Robert Mulroney]

If you have the cells selected then this should work:

Public Sub oneColumn()

Dim targetCell As Range
Dim i As Integer

'Just where we want the list to go
Set targetCell = Range("A1")
i = 1

For Each cl In Selection.Cells
targetCell.Cells(i, 1) = cl.Value
i = i + 1
Next

End Sub


Of couse this will lead to problems if you want to put the information where
your original data is. In that case it's a bit more complicated; you'll need
to put you cells in an array and copy it back on to the sheet:


Public Sub oneColumn()

Dim targetCell As Range
Dim numberOfCells As Integer
Dim source() As Variant
Dim i As Integer

'The first cell in the selection
Set targetCell = Selection.Cells(1, 1)

'Count the nunber of cells.
numberOfCells = Selection.Rows.Count * Selection.Columns.Count

'Redefine our array to be big enough
ReDim source(numberOfCells)

i = 1

'loop through each cell and remember what's in it
For Each cl In Selection.Cells
source(i) = cl.Value
cl.Value = ""
i = i + 1
Next

'Output our original cell at the target
For i = 1 To numberOfCells
targetCell.Cells(i, 1) = source(i)
Next

End Sub

Both of these procedures assume that you have your source rows highlighted.



- Rm





"JenBasch" wrote:

>
> I am trying to figure out how to write a macro to take the contents of
> cells which are across a few columns and move them all into one long
> column.
>
> Example:
>
> |column1| |column2| |column3|
> |dog.......| |bird.......| |cat.........|
> |car........| |truck.....| |bus........|
>
> Into
>
> |column4|
> dog
> bird
> cat
> car
> truck
> bus
>
> It seems like it shouldn't be too hard, I am very new to Macros, so any
> help would be greatly appreciated.
>
> Thanks,
> Jen
>
>
> --
> JenBasch
> ------------------------------------------------------------------------
> JenBasch's Profile: http://www.excelforum.com/member.php...o&userid=27369
> View this thread: http://www.excelforum.com/showthread...hreadid=469032
>
>