Macro to move the contents of a cell

[JenBasch]

I am trying to figure out how to write a macro to take the contents o
cells which are across a few columns and move them all into one lon
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 an
help would be greatly appreciated.

Thanks,
Je

 

[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