Deleting Rows from a single column based on multiple criteria

  • I need to develop a macro that will review a column (A) of Store Numbers and delete the rows that contain specific store numbers. For example, column A that contains 1100 total store numbers and I need to delete the rows containing following numbers: 100, 250, 1200, 1515, 2649. The macro will need to be able to work on a column of any length up to 1500 rows long and delete the example numbers above if they are in the column.

  • Re: Deleting Rows from a single column based on multiple criteria


    You will need to find the last row of data in the column and then work backwards to delete the rows containing that matching data.
    A few assumptions of course:
    1. The number of rows after column A is no greater than the number of rows after column A - this affects how you locate the last row in a column
    2. The sheet containing the data is the first sheet in the current active workbook


    Here's a possible solution:


    There are other ways to find the last row if assumption A is not true.


    Also, of course, you can delete just the cell in column A without the deleting the whole row if desired (and just move the rest of the data up).

  • Re: Deleting Rows from a single column based on multiple criteria


    Hello ldh1217


    Welcome to Ozgrid.


    While gijsmo has provided a viable solution, the criteria are hard-coded into the macro.
    For maximum flexibility, it is best to use a list contained in a range of cells. This way, at will, you may change the criteria and add as many or have as few as is needed.


    Also, to reduce the amount of looping we can improved the efficiency of the process by looping though just the limited number of criteria cells rather than the data cells.


    Give this code a try on BACK UP COPY of the data - VBA actions cannot be undone.



    Note: sheet reference are sheet code names - - not worksheet (tab) names - - change them as required.

  • Re: Deleting Rows from a single column based on multiple criteria


    AAE is right in that the looping is inefficient when compared to using the AutoFilter. My only concern is that the criteria will end up on the worksheet somewhere which may or may not be an issue depending on whether this workbook is meant to be used by an end-user or not. If it is not an issue, then this might be better done with a named range so there is no hard coding for Sheet 2 (in the AAE example).


    Also, if having the criteria data visible and/or possibly available to an end-user is an issue, then changing the hard coded values in the code is no more of a problem than changing the criteria values on a sheet - but it is less visible to an end user.


    One other question I have (for my own education) is what if an AutoFilter was already in place - the code sample would turn that filter off wouldn't it?

  • Re: Deleting Rows from a single column based on multiple criteria


    AAE,
    This is a great option but I am struggling with refining it. I am attempting to establish my list of units to delete as a separate file on a shared drive and have the macro go out and remove anything from "sheet1" (which i will need to be current sheet) from that remote file. This way I can manage my delete list separately from the macro itself. How would I go about adding this to the macro? Thanks so much for this great help.

  • Re: Deleting Rows from a single column based on multiple criteria


    With a small modification to AAE's code, you can change the criteria from being on a worksheet somewhere to being in an external text file - "C:\Criteria.txt" in my example below:


    Note that to declare a FileSystemObject you will need to add Microsoft Scripting Runtime in the VBE. While in the VBE go to Tools-->References and check Microsoft Scripting Runtime.


    The Criteria text file should contain 1 item per line and no blank lines eg:
    100
    250
    1200
    1515
    2649


    Note also that this is "barebones" code, you should think about including some error handling in the code in the event there is a problem reading the text file. You could also put some validation after you read a line from the text file (trim excess spaces, check if it is a number, etc).

Participate now!

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