Results 1 to 2 of 2
2011-05-12, 11:57 #1
- Join Date
- Jan 2001
- 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.
2011-05-12, 16:57 #2
- Join Date
- Nov 2002
- New York, New York, USA
- Thanked 17 Times in 17 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.
VBA Code / Explanation of VBA Code
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
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
Just The Code
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 ))
Last edited by duthiet; 2011-05-12 at 17:15. Reason: Edited To correct formatting