On Tue, 16 Jun 2009 02:35:32 -0700 (PDT), Philipgrae
wrote:

Just seen what a mess the data looks like when posted.....

To explain, there should be four columns in the example, A to D. In
each is a piece of data (Date, Name, Delimited text and Text.

There are four rows, 1 to 4.

In the output, ther are still four columns, with the delimited text
separated out into new rows, thus increasing the number of rows in the
set to 6.

I need the non-delimited data that remains in the set to be copied UP
into the blank cells created by the separation and placement of the
delimited data.

I hope that makes sense!

P ;)


Perhaps not so elegant as Tom's, but it should work to do what you describe.

It was not clear to me from your descriptions if your original data had only
four columns, or if there could be more. I wrote the routine so it would
handle n columns.

Important assumptions:
Delimited text is in column 1
Data Source is a NAME'd range (named on the worksheet).
Destination starts in A1, and a A1.CurrentRegion.ClearContents will not
destroy anything valuable. But you may want to look at this method of clearing
out old data critically before applying it.

=============================
Option Explicit
Sub ReformatData()
Dim rSrc As Range, c As Range, rDest As Range
Dim i As Long, j As Long, k As Long
Dim sTemp() As String
Const sSep As String = ";#"

Set rSrc = Range("OrigDataTbl")
Set rDest = Range("A1")

rDest.CurrentRegion.ClearContents

i = 0
For j = 1 To rSrc.Rows.Count
Set rDest = rDest(1 + i, 1)
Set c = rSrc(j, 1)
sTemp = Split(c.Value, sSep)
For i = 0 To UBound(sTemp)
rDest(i + 1, 1).Value = sTemp(i)
For k = 2 To rSrc.Columns.Count
With rDest(i + 1, k)
.Value = c(1, k)
.NumberFormat = c(1, k).NumberFormat
End With
Next k
Next i
Next j
End Sub
==============================
--ron