Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Why this macro not delete the correct line.... (2

    Sub CANCELLA_ZERO()
    Dim FromR As Range
    Dim ToR As Range
    Dim EndRow_L0953_AUTO As Long
    Dim EndRow_L0953 As Long
    Dim X As Long

    EndRow_L0953_AUTO = Sheets("L0953_AUTOMATICI").Range("A65536").End(xlU p).Row + 1

    Application.Calculation = xlCalculationManual
    X = 3
    While Not Sheets("L0953").Range("A" & X) = ""
    If Sheets("L0953").Range("AA" & X) = "ZERO" Then

    Sheets("L0953_AUTOMATICI").Range("A" & EndRow_L0953_AUTO) = Sheets("L0953").Range("A" & X)
    Sheets("L0953_AUTOMATICI").Range("B" & EndRow_L0953_AUTO) = Sheets("L0953").Range("B" & X)
    Sheets("L0953_AUTOMATICI").Range("C" & EndRow_L0953_AUTO) = Sheets("L0953").Range("C" & X)
    Sheets("L0953_AUTOMATICI").Range("D" & EndRow_L0953_AUTO) = Sheets("L0953").Range("D" & X)
    Sheets("L0953_AUTOMATICI").Range("E" & EndRow_L0953_AUTO) = Sheets("L0953").Range("E" & X)
    Sheets("L0953_AUTOMATICI").Range("F" & EndRow_L0953_AUTO) = Sheets("L0953").Range("F" & X)
    Sheets("L0953_AUTOMATICI").Range("G" & EndRow_L0953_AUTO) = Sheets("L0953").Range("G" & X)
    Sheets("L0953_AUTOMATICI").Range("H" & EndRow_L0953_AUTO) = Sheets("L0953").Range("H" & X)
    Sheets("L0953_AUTOMATICI").Range("I" & EndRow_L0953_AUTO) = Sheets("L0953").Range("I" & X)
    Sheets("L0953_AUTOMATICI").Range("J" & EndRow_L0953_AUTO) = Sheets("L0953").Range("J" & X)
    Sheets("L0953_AUTOMATICI").Range("K" & EndRow_L0953_AUTO) = Sheets("L0953").Range("K" & X)
    Sheets("L0953_AUTOMATICI").Range("L" & EndRow_L0953_AUTO) = Sheets("L0953").Range("L" & X)
    Sheets("L0953_AUTOMATICI").Range("M" & EndRow_L0953_AUTO) = Sheets("L0953").Range("M" & X)
    Sheets("L0953_AUTOMATICI").Range("N" & EndRow_L0953_AUTO) = Sheets("L0953").Range("N" & X)
    Sheets("L0953_AUTOMATICI").Range("O" & EndRow_L0953_AUTO) = Sheets("L0953").Range("O" & X)
    Sheets("L0953_AUTOMATICI").Range("P" & EndRow_L0953_AUTO) = Sheets("L0953").Range("P" & X)
    Sheets("L0953").Range("A" & X).EntireRow.Delete Shift:=xlUp
    EndRow_L0953_AUTO = EndRow_L0953_AUTO + 1
    End If
    X = X + 1
    Wend
    Application.Calculation = xlCalculationAutomatic
    MsgBox "Finished"
    End Sub

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

    Re: Why this macro not delete the correct line.... (2

    Please explain which line you want to delete and which line the macro deletes instead.

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

    Re: Why this macro not delete the correct line....

    in effect i want to cut from L0953 and paste in L_AUTOMATICO all lines with value ZERO in AA of L0953...

    note:
    1) Real wbook can have 35.000 c.a. lines with ZERO in AA
    2) use the copy of range line by line becuse during the transfering i make other calculation on a sigle line copied in L0953_AUTOMATICO...
    3) use better fast metod if is possible
    tks.

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

    Re: Why this macro not delete the correct line....

    If you delete rows in a loop, you must loop backwards. Try this:

    Sub CANCELLA_ZERO()
    Dim EndRow_L0953_AUTO As Long
    Dim EndRow_L0953 As Long
    Dim X As Long
    EndRow_L0953_AUTO = Sheets("L0953_AUTOMATICI").Range("A65536").End(xlU p).Row + 1
    Application.Calculation = xlCalculationManual
    For X = Sheets("L0953").Range("A65536").End(xlUp).Row To 3 Step -1
    If Sheets("L0953").Range("AA" & X) = "ZERO" Then
    Sheets("L0953").Range("A" & X & ":P" & X).Copy _
    Destination:=Sheets("L0953_AUTOMATICI").Range("A" & EndRow_L0953_AUTO)
    Sheets("L0953").Range("A" & X).EntireRow.Delete Shift:=xlUp
    EndRow_L0953_AUTO = EndRow_L0953_AUTO + 1
    End If
    Next X
    Application.Calculation = xlCalculationAutomatic
    MsgBox "Finished"
    End Sub

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

    Re: Why this macro not delete the correct line....

    NICE....and good

    but intstead this:
    Sheets("L0953").Range("A" & X & ":P" & X).Copy _
    Destination:=Sheets("L0953_AUTOMATICI").Range("A" & EndRow_L0953_AUTO)
    not is possible to use this block of instruction similar in a function:
    Sheets("L0953_AUTOMATICI").Range("A" & EndRow_L0953_AUTO) = Sheets("L0953").Range("A" & X)
    Sheets("L0953_AUTOMATICI").Range("B" & EndRow_L0953_AUTO) = Sheets("L0953").Range("B" & X)
    Sheets("L0953_AUTOMATICI").Range("C" & EndRow_L0953_AUTO) = Sheets("L0953").Range("C" & X)
    Sheets("L0953_AUTOMATICI").Range("D" & EndRow_L0953_AUTO) = Sheets("L0953").Range("D" & X)
    Sheets("L0953_AUTOMATICI").Range("E" & EndRow_L0953_AUTO) = Sheets("L0953").Range("E" & X)
    Sheets("L0953_AUTOMATICI").Range("F" & EndRow_L0953_AUTO) = Sheets("L0953").Range("F" & X)
    Sheets("L0953_AUTOMATICI").Range("G" & EndRow_L0953_AUTO) = Sheets("L0953").Range("G" & X)
    Sheets("L0953_AUTOMATICI").Range("H" & EndRow_L0953_AUTO) = Sheets("L0953").Range("H" & X)
    Sheets("L0953_AUTOMATICI").Range("I" & EndRow_L0953_AUTO) = Sheets("L0953").Range("I" & X)
    Sheets("L0953_AUTOMATICI").Range("J" & EndRow_L0953_AUTO) = Sheets("L0953").Range("J" & X)
    Sheets("L0953_AUTOMATICI").Range("K" & EndRow_L0953_AUTO) = Sheets("L0953").Range("K" & X)
    Sheets("L0953_AUTOMATICI").Range("L" & EndRow_L0953_AUTO) = Sheets("L0953").Range("L" & X)
    Sheets("L0953_AUTOMATICI").Range("M" & EndRow_L0953_AUTO) = Sheets("L0953").Range("M" & X)
    Sheets("L0953_AUTOMATICI").Range("N" & EndRow_L0953_AUTO) = Sheets("L0953").Range("N" & X)
    Sheets("L0953_AUTOMATICI").Range("O" & EndRow_L0953_AUTO) = Sheets("L0953").Range("O" & X)
    Sheets("L0953_AUTOMATICI").Range("P" & EndRow_L0953_AUTO) = Sheets("L0953").Range("P" & X)

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

    Re: Why this macro not delete the correct line....

    Yes, that is possible, but it will be a lot slower.

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

    Re: Why this macro not delete the correct line....

    hummmmm
    but to speed my code have other idea? impotant for me to use the copy cell by cell...

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

    Re: Why this macro not delete the correct line....

    If you copy cell by cell it will always be slow. But you can improve speed by not updating the screen while the macro runs: insert the line

    Application.ScreenUpdating = False

    at the beginning of the macro, and

    Application.ScreenUpdating = True

    at the end.

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

    Re: Why this macro not delete the correct line....

    tks for adjust my code and suggestion.
    Sal.

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

    Re: Why this macro not delete the correct line....

    On fly on this sheet...
    why the first CEL in RNG1 is FIL. and not 4500
    Piece of code:

    ........

    Set RNG1 = WS.AutoFilter.Range

    WS.Range("AA3:AA65536").ClearContents
    WS.Range("AA1") = 0

    For SPORT = 2 To Worksheets("GRUPPO").Range("L1").Value
    SPORTELLO = Worksheets("GRUPPO").Range("A" & SPORT).Value
    WS.Range("I1").Value = SPORTELLO
    For SOSP = 2 To Worksheets("GRUPPO").Range("K1").Value
    SOSPESO = Worksheets("GRUPPO").Range("J" & SOSP).Value
    WS.Range("H1").Value = SOSPESO

    RNG1.AutoFilter Field:=12, Criteria1:=SPORTELLO
    RNG1.AutoFilter Field:=4, Criteria1:=SOSPESO

    'Set RNG1 = WS.AutoFilter.Range

    FROWS = 0
    With WS.AutoFilter.Range
    Set RNG1 = .Columns(12).SpecialCells(xlCellTypeVisible)
    FROWS = RNG1.COUNT
    End With
    'CHIUSI = 0

    If FROWS > 1 Then

    SOMMA = SOMMA * 1
    SOMMA = 0
    'CHIUSI = 0

    For Each CEL In RNG1
    If CEL = SPORTELLO And CEL.Offset(0, -8).Value = SOSPESO Then
    RIGA = CEL.Row
    SOMMA = Range("I" & RIGA).Value + SOMMA
    'If CEL.Offset(0, 14) > "" Then
    'CHIUSI = CHIUSI + 1
    'End If
    End If
    Next CEL
    ....

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

    Re: Why this macro not delete the correct line....

    The AutoFilter range includes the field names (column headers) of the table.

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

    Re: Why this macro not delete the correct line....

    not possible to set first CEL after header?

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

    Re: Why this macro not delete the correct line....

    You could do this:

    Set RNG1 = WS.AutoFilter.Range
    Set RNG1 = RNG1.Offset(1, 0).Resize(RNG1.Rows.Count - 1)

    This will exclude the header row from the range.

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

    Re: Why this macro not delete the correct line....

    Hans sorry me but the first value in CEL i still have always FIL...

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

    Re: Why this macro not delete the correct line....

    You set RNG1 several times, you will have to offset and resize it each time.

Page 1 of 2 12 LastLast

Posting Permissions

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