Results 1 to 2 of 2
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Macro For Deleting Rows When Certain Number/Text Appears In Certain Cell

    My worksheet contains an inventory of over 1000 auto parts. Column A is the part number, a unique number for each row (i. e., no cell in column A contains the same number); column B contains the part description, and these cells may be duplicated; column C contains the price, and, like column B, the cells in column C may contain duplicates; and finally, column D contains the quantity of each part in inventory, which, again, like columns B and C, may contain duplicates. What I would like is a macro that would delete any row with a certain part number in column A. There may be 40-50 different part numbers that I would like to include in this deleting process. I thought about filtering, but that is too time-cousuming. Any ideas?
    Thanks in advance.

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Below is VBA code that should assist.

    Before you use the Code you should give a Range Name in the Sheet for the Column of Data to be evaluated, that way it much easier to run multiple part numbers.
    If you have a lot of part numbers consider adding an outside loop.
    The below Code is currently set to use the part number in cell B5 of the active sheet, which you can modify to what you need.

    Good Luck.

    VBA Code / Explanation of VBA Code
    Sub MyList()
    Dim Rng as Range Explicit Variable Declaration
    Dim RngMatch as Variable Explicit Variable Declaration
    Dim C_Rows as Integer Explicit Variable Declaration
    Dim r as Integer Explicit Variable Declaration
    Set Rng = Selection Set variable Rng to equal the Current Selection
    Set RngMatch = Nothing Set variable RngMatch as a Void Range
    C_Rows = Rng.Rows.Count Create Variable C_Rows to equal the number of total rows in the Selection
    For r = 1 to C_Rows Begin the Loop that will evaluate every cell in the Selection
    If Rng.Cells(r, 1).Value = Range("B5").Value Then Start the IF statement that searches all the cells in the
    Selection for the value contained in Cell B5 of the active
    Worksheet
    If RngMatch Is Nothing Then When a Cell equals the values in B5 and it is the first instance
    RngMatch is Set to that Cell Reference
    Set RngMatch = Rng.Cells(r, 1)Else Set RngMatch = Application.Union(RngMatch, Rng. Cells (r, 1 ))When a Cell equals
    the values in B5 and it is a repeat instance Rng
    Match is Set to bethe Old RngMatch Range
    Object collection Plus this new instance
    End IF End The First IF Statement
    End IF End The Second IF Statement
    Next r Continue the Loop
    RngMatch.Select Select RngMatch which now includes all the rows to deleted.
    Selection.EntireRow.Delete Delete the Rows
    End Sub



    Just The Code

    Sub MyList()
    Dim Rng as Range
    Dim RngMatch as Variable
    Dim C_Rows as Integer
    Dim r as Integer
    Set Rng = Selection
    Set RngMatch = Nothing
    C_Rows = Rng.Rows.Count
    For r = 1 to C_Rows
    If Rng.Cells(r, 1).Value = Range("B5").Value Then
    If RngMatch Is Nothing Then
    Set RngMatch = Rng.Cells(r, 1)
    Else Set RngMatch = Application.Union(RngMatch, Rng. Cells (r, 1 ))
    End IF
    End IF
    Next rRngMatch.SelectSelection.EntireRow.Delete
    End Sub
    Last edited by duthiet; 2011-05-12 at 18:15. Reason: Edited To correct formatting

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •