vb script structure advice

[escellentguy]

Hello-

I am working with an end user that receives a weekly EDI transmission which consolidates requests for shipments from three companies. The report is essentially 5 columns. The first column lists the company that requested and the other 4 columns list part numbers and other identifying product numbers.

Only the first row of the colum for the company is filled in for the entire company order and the remaining rows are blank for that column. The companies are separated by junk lines which read across the row PACKING ORDER TO FOLLOW. The EDI transmission looks like this.

Company A Part1 Another Number Another Number
Part 2 "" ""
Part 3 "" ""
PACKING ORDER TO FOLLOW
Company B Part 1 "" ""
Part 2 "" ""

The first thing I would like to automate for them is to fill in the company name all the way down the column so that later retrieval of the information will have a company assigned to each part. Then I would like to delete the junk rows that say packing order to follow.

Would it be wise to have the vb script find nulls in column A and duplicate the first non null to fill in the company or is there a better way to normalize the data to structure it more similar to a database? They are currently filling in the company name then copying and pasting each company into a separate spreadsheet to separate them in case they have to retrieve the info later. I would foresee a user interface panel or control panel similar to access where they can select which company to view and which dates to view and it would pull from the master spreadsheet rather than storing the information in hundreds of separate spreadsheets.

Just wanted to get some insight from the masters before I begin to structure this. Any thoughts welcomed.

Thank you in advance.

 

[SkipVought]

Hi,

Give this a try

Sub Macro()
    Dim lRow As Long
    
    With ActiveSheet.[A1].CurrentRegion
        For lRow = .Rows.Count To 2 Step -1
            With .Cells(lRow, 1)
                Select Case Trim(.Value)
                    Case ""
                        .Value = .End(xlUp).Value
                    Case "PACKING ORDER TO FOLLOW"
                        .EntireRow.Delete xlUp
                End Select
            End With
        Next
    End With
End Sub

Skip,
[sub]
[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [/sub]

 

[escellentguy]

wow! thank you very much i certainly will give that a try!!!! Many thanks!

 

[escellentguy]

oh...its giving me a syntax error on 1Row....i should have mentioned that i am using office 2003 if that matters

 

[PHV]

Replace the 1 (one) with l (lowercase ell)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

OOPS! [BLUSH]

Skip,
[sub]
[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [/sub]

 

[escellentguy]

oops is right. lol thanks again!

 

[escellentguy]

by the way it was my oops i was commenting on not yours