Results 1 to 11 of 11
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete line if.... (2000 sr 1)

    In this wbook is present a macro to import a txt file. (attached into zip T8327.epf).
    The macro controll with this code to not import a line/record alreday existis into sheet, and insert only the value just imported:

    'CONTROLL DUPES ON INDEX COLUMN AC
    INDEX = var_DIP & "-" & var_SEDE & "-" & var_CERT & "-" & var_RATA & "/" & var_ANNO

    Set Found_INDEX = ELENCO.Columns("AC:AC").Find((INDEX), LookIn:=xlFormulas)

    If Found_INDEX Is Nothing Then
    'CONTROLL DUPES ON INDEX COLUMN AC

    the macro work fine...

    my problem:

    is possible during the txt import to delete the entire line (range A:AD) from A.T. CAMPNIA and nsert into sheet DEFINITE the line in question, if from next import of txt file the index from new import not is present into column AC of A.T. CAMPANIA...

    example: index in column AC of A.T. CAMPANIA is 4500-5100-06302230-12/2004
    into next import not existis this index, delete the entire line (range A:AD) from A.T. CAMPANIA and insert into sheet DEFINITE...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete line if.... (2000 sr 1)

    You don't know if an index in column AC does not occur in the text file until you have imported all lines. But if you import directly into A.T.CAMPANIA, it's hard to say which lines are new and which already existed. So you should do the following:
    - Import all lines from the text file into a new worksheet (not into A.T.CAMPANIA)
    - Loop through the cells in column AC on A.T.CAMPANIA and see if there is a match in the corresponding column on the new sheet. If there is a match, the line in the new sheet is a duplicate, so it can be deleted, otherwise, the line in A.T.CAMPANIA can be copied to DEFINITE and deleted from A.T.CAMPANIA.
    - When the loop is finished, you can copy the remaining lines from the new worksheet into A.T.CAMPANIA.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete line if.... (2000 sr 1)

    (Edited by Leif to make link live - see the quick guide and/or <!help=19>Help 19<!/help>. If you are looking for assistance, it is very helpful - and easy - to add the URL tags yourself.)

    http://www2.rapidupload.com/d.php?file=dl&filepath=2332
    Sorry for dimension is out only to 44k to 100k...
    I have maked a macro DEFINITE but naturally not W...
    The sheet contian the new import is hide and is named SERVIZIO...
    Tks, Hans.

    NOTE: inested A.T. CAMPANIA i have renamed the sheet in RATE, sorry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete line if.... (2000 sr 1)

    You must compare the imported values in SERVIZIO with the values in RATE. Your code tries to compare DEFINITE and RATE.

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete line if.... (2000 sr 1)

    Tks Hans but not for me...
    New macro:
    Sub DEFINITE()
    Dim N As Integer, PR As Integer, C As Integer, S1 As String, S2 As String
    Dim RNG As Range
    N = 3 'PRIMA RIGA RATE SHEET
    PR = Worksheets("RATE").Range("A65536").End(xlUp).Row + 1 'first paste row on PAGATI sheet
    C = 1

    Application.ScreenUpdating = False

    S1 = "SERVIZIO"
    S2 = "RATE"
    S3 = "DEFINITE"

    Sheets(S2).Select

    Do Until Cells(N, 1) = ""
    If (Val(Cells(N, 29)) = Val(Cells(N + 1, 29))) And Cells(N, 29) <> Cells(N + 1, 29) Then

    If Sheets(S1).Range("AC:AC").Find(Cells(C, 29)) Is Nothing Then
    Range(Cells(C, 1), Cells(C, 29)).Copy
    Sheets(S3).Select
    Cells(PR, 1).PasteSpecial xlPasteValues
    PR = PR + 1
    Sheets(S2).Select
    End If
    Rows(N & ":" & N + 1).Delete
    Else
    100
    N = N + 1
    End If
    Loop

    Application.ScreenUpdating = True
    End Sub

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete line if.... (2000 sr 1)

    I don't understand why you compare lines within the RATE sheet with each other. Try this:

    Sub DEFINITE()
    Dim N As Long, PR As Long, S1 As String, S2 As String, S3 As String
    Dim rng As Range

    Application.ScreenUpdating = False

    S1 = "SERVIZIO"
    S2 = "RATE"
    S3 = "DEFINITE"

    N = 3
    PR = Worksheets(S3).Range("A65536").End(xlUp).Row + 1

    ' Test for matches between S1 and S2
    With Sheets(S2)
    Do Until .Cells(N, 1) = ""
    Set rng = Sheets(S1).Range("AC:AC").Find(.Cells(N, 29))
    If rng Is Nothing Then
    ' No match - copy row from S2 to S3
    .Range(.Cells(N, 1), .Cells(N, 29)).Copy
    Sheets(S3).Cells(PR, 1).PasteSpecial xlPasteValues
    .Rows(N).Delete
    PR = PR + 1
    Else
    ' Match - delete row from S1
    rng.EntireRow.Delete
    N = N + 1
    End If
    Loop

    ' Copy remaining rows from S1 to S2
    PR = Sheets(S1).Range("A65536").End(xlUp).Row
    If PR > 2 Then
    Sheets(S1).Range("3:" & PR).Copy .Range("A" & N)
    Sheets(S1).Range("3:" & PR).Delete
    End If
    End With

    Application.ScreenUpdating = True
    End Sub

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete line if.... (2000 sr 1)

    WORK FINE!!!!
    For this: "I don't understand why you compare lines within the RATE sheet with each other."...

    i dont understand not even I, peraphs my head is in the first fired fase;-(

    Tks for help, as usual
    and naturally for patience, i have maked in other case a code, bad, but maked.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete line if.... (2000 sr 1)

    You know the row number to which the line is copied (PR), so it should be easy to insert a line that sets the date in column AE in that row.

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete line if.... (2000 sr 1)

    ACCC....
    How can to insert in column AE the date in this format DD/MM/YYYY , during the copy lines into sheet DEFINITE...

    dont worry solved!

  10. #10
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete line if.... (2000 sr 1)

    from my home... after 3Hs of work wath you think obout your "student", post you comment on my code...
    Sub prova()
    Set fineR = Sheets("RATE").Range("AC65000").End(xlUp)
    Set fines = Sheets("SERVIZIO").Range("AC65000").End(xlUp)
    Set e = Intersect(Sheets("RATE").Range("a2").CurrentRegion , Sheets("RATE").Columns("A:AD"))
    Set a = Range("A65000:AD65000")
    Set b = Application.Range(Sheets("SERVIZIO").Range("AC3"), fines)
    For i = 3 To fineR.Row
    With b
    Set C = .Find(Sheets("RATE").Cells(i, 29), LookIn:=xlValues, LookAt:= _
    xlWhole)
    If Not C Is Nothing Then
    GoTo vai
    Else
    Set a = Union(a, Intersect(Sheets("RATE").Cells(i, 29).EntireRow, e))
    End If
    End With
    vai:
    Next
    a.Copy Sheets("DEFINITE").Range("A3")
    a.EntireRow.Delete

    End Sub

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete line if.... (2000 sr 1)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •