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

    filtre and delete (2000 sr 1)

    i have maked this macro to delete antire cells in range A:AC but not work....
    assuming the var have in this case the value:

    FILTRO1 = VAR_PERIODO = DECADALE
    FILTRO2 = DATA=10/01/2006

    In effect if the condition existi delete all visble cell filtred from A:AC...

    Sub FILTRO(VAR_PERIODO, DATA)

    Dim FILTRO1 As String
    Dim FILTRO2 As String
    Dim lngMaxRow As Long

    FILTRO1 = VAR_PERIODO
    FILTRO2 = DATA

    With Sheets("INQUIRY")
    lngMaxRow = .Range("A65536").End(xlUp).Row
    .Range("A5").AutoFilter Field:=3, Criteria1:=FILTRO1
    .Range("A5").AutoFilter Field:=8, Criteria1:=FILTRO2
    .Range("A5:AC" & lngMaxRow).SpecialCells(xlCellTypeVisible). _
    EntireRow.Delete
    .ShowAllData
    End With

    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: filtre and delete (2000 sr 1)

    Change:
    .Range("A5:A<font color=red>C</font color=red>" & lngMaxRow).SpecialCells(xlCellTypeVisible). _
    EntireRow.Delete

    to
    .Range("A5:A" & lngMaxRow).SpecialCells(xlCellTypeVisible). _
    EntireRow.Delete

    You only need to use 1 column of the data. If you use more than 1 you get overlapping ranges.You can't delete overlapping ranges.

    Steve

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

    Re: filtre and delete (2000 sr 1)

    ...Good, i am a stupid!
    Tks.

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

    Re: filtre and delete (2000 sr 1)

    Try switching SpecialCells and EntireRow:

    .Range("A5:AC" & lngMaxRow).EntireRow.SpecialCells(xlCellTypeVisibl e).Delete

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

    Re: filtre and delete (2000 sr 1)

    Tks Hans....
    But when the mcaro start request me with a msgbox "delete the line?" ??????
    .... and in other case the macro delete also all value in column AD!

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

    Re: filtre and delete (2000 sr 1)

    Since you had EntireRow in the code, I assumed that you wanted to delete the entire row. If you only want to delete columns A through AC, you must omit EntireRow. To avoid the message box, specify that you want to shift cells up:

    .Range("A5:AC" & lngMaxRow).SpecialCells(xlCellTypeVisible).Delete Shift:=xlShiftUp

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

    Re: filtre and delete (2000 sr 1)

    ...sure now i have understand, sorry for bad explain.

    sorry Hnas, I have forgotten one notice a lot important, before to post: Good morning

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

    Re: filtre and delete (2000 sr 1)

    Hans i have maked this macro to ordering the value into sheet INQUIRY, but not sure that work correctlly, test form me please...

    Sub ORDINA()

    Application.ScreenUpdating = False
    Range(Range("H5"), Range("H5").End(xlDown)).Offset(0, 3).FormulaR1C1 = "=DATEVALUE(RC[-3])"
    Range(Range("A5:K5"), Range("A5:AC5").End(xlDown)).Sort _
    Key1:=Range("K5") _
    , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
    Range("K:K").ClearContents
    Application.ScreenUpdating = True
    End Sub

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

    Re: filtre and delete (2000 sr 1)

    In the workbook you attached in the first post in this thread, all cells in column H contain the same value "10/01/2006", so sorting on the date value of this column has no effect. If I change some of the values in column H, the values are sorted correctly.

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

    Re: filtre and delete (2000 sr 1)

    Now... i have modified my macro and re format column with new param... clear all i have posted ago

    My idea:

    - Set my range A:AC

    key to ordering:

    - key1 column A
    - key2 column C (in effect it can assuming varius param from a var. In this case assuming the var is DECADALE)
    - key3 column AB (i have formatted the column in date format)

    primary key index is column A

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

    Re: filtre and delete (2000 sr 1)

    In the workbook you attached, column C contains the same value in all rows, and column AB too, so sorting on them has no effect. But the code to sort on A, C and AB is:

    Range(Range("A5:AC5"), Range("A5:AC5").End(xlDown)).Sort _
    Key1:=Range("A5"), Key2:=Range("C5"), Key3:=Range("AB5"), _
    Header:=xlNo

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

    Re: filtre and delete (2000 sr 1)

    WORK PERFECT!
    ... Litle modify

    assuming in C are present all this value, is possble to respect this order, from 01>11. In effect from min to max (01 is the min and 11 the max):

    01-GIORNALIERA
    04-DECADALE
    05-QUINDICINALE
    06-MENSILE
    08-TRIMESTRALE
    10-SEMESTRALE
    11-ANNUALE

    hope you have understand me.... sure!

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

    Re: filtre and delete (2000 sr 1)

    What are the actual values in column C? GIORNALIERA etc., or 01-GIORNIALIERA etc.?

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

    Re: filtre and delete (2000 sr 1)

    ...sorry for bad table

    GIORNALIERA
    DECADALE
    QUINDICINALE
    MENSILE
    TRIMESTRALE
    SEMESTRALE
    ANNUALE

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

    Re: filtre and delete (2000 sr 1)

    Is this to be used just on your PC, or by others too?

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
  •