Cleaning up data imported into excel 1

[Davidprince]

I am trying to export customer address data from MYOB Accounts Software into Excel and then import it into a filemaker pro Database.
The addresses are coming out of MYOB into excel in a virtually unuseable way.
There are column headings:
1) Contact 2) Company Name 3) Phone 4) Address1

The problem is in Column 4 each line is put in a new row and so different contacts have a variable numbers of rows.
1) Contact 2) Company Name 3) Phone 4) Address1
Perplexed Unknown Pty Ltd 0235689 67 Try Street
JUNO
VERMONT
Perplexed Unknown Pty Ltd 0235689 Unit 1
25 Try Street
SYDNEY
NSW 2000

Any advice gratefully accepted.
David

 

[Dreamboat]

Hi, David.

YOu could try something like this:

Sub FixFormat()
Dim LastRow As Long
Dim x As Long
LastRow = Range("D65536").End(xlUp).Row
For x = LastRow To 2 Step -1
If Range("B" & x).Value = "" Then
Range("D" & x - 1).Value = Range("D" & x - 1).Value & " " & Range("D" & x).Value
Range("D" & x).ClearContents
End If
Next x

End Sub



Anne Troy

http://www.vbaexpress.com/forum

 

[Dreamboat]

Sorry, David.
Please change the one line (near the very end, the last line that begins with "range") to:

Range("D" & x).EntireRow.Delete

Anne Troy

http://www.vbaexpress.com/forum