Delete Excel Rows Using Auto Filtering .SpecialCells(xlCellTypeVisible).EntireRow.Del

  • in reference to http://www.ozgrid.com/VBA/row-delete-criteria.htm


    Specifically:


    Code
    1. With rRange 'Filter, offset(to exclude headers) and delete visible rows
    2. .AutoFilter Field:=Col1, Criteria1:=strCriteria1
    3. .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    4. End With


    Code works great for deleting visible rows until i try to remove rows based on a criteria of:

    Code
    1. strCriteria3 = "*----*"


    The data filters correctly using this criteria but when I get to the:


    Code
    1. .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete


    Portion of the code. The rows are not deleted.


    Do you know of any issues with having nothing but


    [TABLE="width: 1000"]

    [tr]


    [td]

    ------

    [/td]


    [td]

    -------------------------

    [/td]


    [td]

    --------------------------------

    [/td]


    [td]

    -----------------------------------------------------------

    [/td]


    [/tr]


    [/TABLE]


    in a row and why this code would fail on those lines?


    Thank you,
    Donald

  • Re: Delete Excel Rows Using Auto Filtering .SpecialCells(xlCellTypeVisible).EntireRow


    I don't see an immediate problem with it, providing you are filtering on the variable in the second code section. The first refers to a different variable but I am assuming that is just an example. Could you attach the file or full code if you are still having problems?

  • Re: Delete Excel Rows Using Auto Filtering .SpecialCells(xlCellTypeVisible).EntireRow


    When I get to this line while steping through the code:


    Code
    1. .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete


    For strCriteria3


    It does not delete visible cells. It does for you?


    Code
    1. With rRange 'Filter, offset(to exclude headers) and delete visible rows
    2. .AutoFilter Field:=Col1, Criteria1:=strCriteria3
    3. .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    4. End With


    This is what I am left with:
    [ATTACH=CONFIG]44316[/ATTACH]

  • Re: Delete Excel Rows Using Auto Filtering .SpecialCells(xlCellTypeVisible).EntireRow


    Yes it does. I assume you have run the code fully through and are still left with the dashed rows. Is that right?


    Have you tried?

    Code
    1. .Offset(1, 0).EntireRow.Delete
  • Re: Delete Excel Rows Using Auto Filtering .SpecialCells(xlCellTypeVisible).EntireRow


    Ahhh, yes assumed it was the working file. Must stop that! Glad you solved it.