View Single Post
  #13   Report Post  
Posted to microsoft.public.excel.misc
Ken Johnson
 
Posts: n/a
Default Excel should merge cells without removing content of any cell

Hi Richard,
try this out...

Public Sub CutPaste_ColB_into_ColA()
Application.ScreenUpdating = False
Dim iRows As Long
Dim iColumns As Long
Dim vaMergeArray As Variant
Dim I As Long
vaMergeArray = Selection
iRows = Selection.Rows.Count
iColumns = Selection.Columns.Count
If iColumns < 2 Then Exit Sub
For I = 1 To iRows
vaMergeArray(I, 1) = vaMergeArray(I, 1) & Space(1) &
vaMergeArray(I, 2)
vaMergeArray(I, 2) = ""
Next I
Selection = vaMergeArray
Selection.Columns.AutoFit
If Application.CountA(ActiveCell.Offset(0, 1).EntireColumn) = 0 Then
ActiveCell.Offset(0, 1).EntireColumn.Delete
End If
End Sub

I'm not sure if this is what you are after.

Make your selection first, then run the macro.
If you select more than two columns nothing happens.
If you select two columns then the values in the first column are
concatenated with the corresponding values in the second column along
with an intervening space.
The second column in the selection is emptied.
If this results in the entire second column being empty then that
column is deleted.
Ken Johnson