compact cells

  • Thread starter Thread starter cathal
  • Start date Start date
C

cathal

A colleague created a script to extract information from certain MS-Wor
documents [containing key fields] into excel. Thus 1 row (15 cells
would contain all the information extracted.

However,some of the information is occupying several cells per column.
Short of using the merge and centre button on a per cell basis, wha
can I do to ensure that extracted information per document occupies
row only?

thanks, catha
 
cathal,

Select all the cells, including the "overflow", and run the macro below. I
assumed you would use a space to separate the values when multiple cells are
compacted - you can easily change that.

HTH,
Bernie
MS Excel MVP

Sub CompactCells()
Dim myCell As Range
Dim i As Integer

For Each myCell In Selection.Columns(1).Cells
For i = 2 To Selection.Columns.Count
If myCell(1, i).Value <> "" Then
myCell.Value = myCell.Value & " " & myCell(1, i).Value
myCell(1, i).ClearContents
End If
Next i
Next myCell

End Sub
 
Back
Top