Macro selecting search criteria from a table

  • Hi all,


    I have this macro below which might not be the fastest but seems to work pretty well. I need some help on having it pick up value criteria from a table rather than manually inputting it as I have currently 20 that I need to search for and delete and might have some more so ideally something where it can pick up a larger list and then search for true values within the list that would be ideal. Currently I have the list of values I want to search for to delete rows that include that value, they are on K16 to K35 but I might expand the list so if its possible to do up to for e.g. K40 and search for true values in the list that would be ideal.


    I also have the list on a different sheet caller Drivers and the dataset I want to remove values from is called Import Data, I want to be able to run the macro from the Drivers page from a button so I also need a line adding to do the search on the Import Data page rather than the active sheet.


    Can someone thing of an easy solution for this either adding in a couple of lines or if its easier then doing the macro a different way?


    Thanks in advance.


    Sub RemoveCode()


    Dim x As Long, lastrow As Long
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    For x = lastrow To 1 Step -1
    If Cells(x, 6).Value = "XXXX" Then
    Rows(x).Delete
    End If
    Next x


    End Sub

  • Hi,


    You could test the following macro ... on a copy of your actual workbook ...



    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)


  • Hi,


    Thank you, I am getting a Runtime 1004 error on this row : .Offset(1, 0).Resize(lastrow - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete


    Any idea on what it could be?

  • Hi,


    Thanks for attaching a sample file with your next message ...


    Probably one of the codes in your K range ... does not appear at all ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hi,


    Thanks for attaching a sample file with your next message ...


    Probably one of the codes in your K range ... does not appear at all ... :wink:



    I took a look through and realised where its going wrong. The list of values I want to filter against is on a sheet called Drivers and the data I want to delete rows from is on a sheet called Import Data on the same file.


    Is there a way to have it switch between the sheets? I want to run the macro from the Driver sheet with the criteria values on it.

  • Hi again,


    Glad to hear we are heading in the right direction ...



    Hope this corresponds to your actual situation

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)


  • That worked an absolute treat, thank you. Is there a way to add some sort of error correction so if one value doesn't exist then it would skip it rather than crash the macro?


    Also I know there is a very simple way to add a pop up message at the end of the macro however I don't remember from years ago when I learned. Could you drop a line at the end of the macro to add a pop up box with a message saying "Complete"?

  • Glad we are progressing ...


    To take into account your two latest requests ...



    Hope this will be fine ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)


  • Hi,


    That is absolutely perfect, thank you.

  • Hi,


    That is absolutely perfect, thank you.



    Thanks ... for your Thanks ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

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