Thanks for your efforts Joel, that works superbly.
--
Cheers,
MarkN


"joel" wrote:


This should work

Sub ReadData()
'
Set DestSht = Sheets("sheet1")

'
fileToOPen = Application _
.GetOpenFilename("Text Files (*.txt), *.txt")
If fileToOPen = False Then
MsgBox ("Cannot OPen file - Exiting Macro")
Exit Sub
End If

Workbooks.OpenText Filename:=fileToOPen, _
DataType:=xlDelimited, Comma:=True
Set bk = ActiveWorkbook

Set SourceSht = bk.Sheets(1)

NewRowCount = 1
With SourceSht
OldRowCount = 1
Do While .Range("A" & OldRowCount) < ""
LastCol = .Cells(OldRowCount,
Columns.Count).End(xlToLeft).Column
If LastCol = 3 Then
ColAHeader = .Range("A" & OldRowCount)
ColBHeader = .Range("B" & OldRowCount)
ColCHeader = .Range("C" & OldRowCount)
Else
Set CopyRange = _
.Range("A" & OldRowCount & ":E" & OldRowCount)
With DestSht
.Range("A" & NewRowCount) = ColAHeader
.Range("B" & NewRowCount) = ColBHeader
.Range("C" & NewRowCount) = ColCHeader
CopyRange.Copy _
Destination:=.Range("D" & NewRowCount)
NewRowCount = NewRowCount + 1
End With
End If
OldRowCount = OldRowCount + 1
Loop

End With


bk.Close savechanges:=fales


End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=175143

Microsoft Office Help

.