Friday, February 26, 2010

Merge excel cell values ; Retain Boundaries

I'm working with excel workbooks for more than 6 hours a day. I needed a quick way to consolidate values of selected cells of a column in the top most row.
e.g.
To merge values of cells 41 to 45 of column B, select the cells and hit the macro. All the values will be consolidated in cell 41.

Sub concate_cell_values()
' This macro consolidates the values
' of the "selected" cells in the top most cell
' Respects cell boundaries

Dim Rng1 As Range
Dim op As String
Dim col, ro As Integer
col = 0
ro = 0

Set Rng1 = Selection

For Each cell In Rng1
cell.Activate
If col = 0 Then
col = ActiveCell.Column
ro = ActiveCell.Row
End If
op = op & cell & " "
Next cell

Cells(ro, col).Value = Trim(op)

End Sub

No comments:

Post a Comment