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

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


    Specifically:


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


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

    Code
    strCriteria3 = "*----*"


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


    Code
    .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
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete


    For strCriteria3


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


    Code
    With rRange 'Filter, offset(to exclude headers) and delete visible rows
          .AutoFilter Field:=Col1, Criteria1:=strCriteria3
          .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        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
    .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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!