formatting and deleting cells and rows from a txt import in excel

[gavin85306]

I believe I have exhausted all the searches I could do on this subject and since I have not solved my first problem I hope I can post and get an answer for both of my issues...

Background:
I recieve a report from an old database in email as a message, I copy that message to word and save it as a txt file. I then import that into excel as a space delimited file.

Issues:
1. 80% of the names that are imported are simply first and last however I have about 20% that have a middle initial with a period. This is causing those lines to shift right one column and throwing off the columns.

2. I get about 200 reports a month, some of them are very large and the system that sends them breaks them up into pages...this causes the import to copy that header information every 36 rows in which I have to delete the following 11 rows.

I would like a vb script that in problem one deletes any cell with a period in it (there are no other cells that have a period in it) and shift left...I have tried the numerous posts but most all are for numbers and I cant seem to get any of them to work...

Of course the second piece is for a once over pass that for every 36 lines the next 11 would be automatically deleted.

I am racking my brain on this and have tried recording macros but it does not record all of my actions and I am at a loss...thank you in advance for any assistance...

 

[hateme28]

Give this a try:

(note: code is untested)

Sub test()
Dim rw As Integer

With Range("a:z")
     Set x = .Find(".", LookIn:=xlValues)

     Do While Not x Is Nothing
         Set x = .Find(".", LookIn:=xlValues)
         If Not x Is Nothing Then
               x.Delete (xlToLeft)
         End If
     Loop
End With

Do
     rw = 36
     Range(Cells(rw, 1), Cells(rw + 11, 1)).EntireRow.Delete (xlUp)
     rw = rw + 36
Loop Until Cells(rw + 11, 1).Value = ""

End Sub

~Matt

 

[gavin85306]

Matt,

Thank you for such a quick reply...I tested it and the cell deletion worked!! Thank you I spent 6 hours trying to do that yesterday...the row deletion worked also for the first encounter of the 36 rows and then it deleted 11, but did not continue for through the rest of the worksheet...