Delete row if Column H contains any of the following values - VBA

[CC268]

I simply want to delete a row if Column H contains any of the following values:
-%
-Resistor
-Capacitor
-MCKT
-Connector
-anything else I may want to add to this list...


Thanks!

 

[CC268]

It won't skip rows because there are two "counters".
LastRowNum is the last row number. The macro will check that many rows.
ReadRow is the row being checked. It only increases by one when the row doesn't meet the criteria (under the Else bit of the macro. Where one of the criteria are met and the row is deleted, ReadRow isn't changed, and the same row number is checked again. So if row 10 is deleted, row 11 becomes row 10 and row 10 is checked again.

Thanks for explaining that!

 

[CC268]

Here is another macro that you can consider (add any new items to the comma delimited quoted list that I show in blue below)...

Not sure why but when I tried your code it deleted all the rows containing "%", but none containing resistor or any of the other categories.

 

[Rick Rothstein]

Not sure why but when I tried your code it deleted all the rows containing "%", but none containing resistor or any of the other categories.

I am sorry, I misread one of your previous messages to say you want the search to be case sensitive, but now I see you wanted case insensitive. Here is the revision (only one thing needed to be changed... one True to a False)...

Sub DelColH()
  Dim V As Variant, DeleteMe As Variant
  DeleteMe = Array("%", "Resistor", "Capacitor", "MCKT", "Connector")
  For Each V In DeleteMe
    Columns("H").Replace "*" & V & "*", "#N/A", xlWhole, ,False, False, False
  Next
  On Error Resume Next
  Columns("H").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
End Sub

 

[Zshanar69]

I'm assuming that you're looking for exact matches (i.e. not the specified word in the middle of a sentence)? If so, try this macro, adding any extra lines you need where marked:

Sub DelColH()
LastRowNum = Cells.SpecialCells(xlCellTypeLastCell).Row
ReadRow = 1
For n = 1 to LastRowNum
  If Range("H" & ReadRow).Value = "%" Or _
    Range("H" & ReadRow).Value = "Resistor" Or _
    Range("H" & ReadRow).Value = "Capacitor" Or _
    Range("H" & ReadRow).Value = "MCKT" Or _
    'Add similar lines here for anything else that you want to delete.  Last line must end with Then, not Or _.
    Range("H" & ReadRow).Value = "Connector" Then
      Range("H" & ReadRow).EntireRow.Delete
    Else
      ReadRow = ReadRow + 1
    End If
  Next
End Sub

What if I wanna start from H4 in column "H" what changes should be made in this code!

 

[Joe4]

What if I wanna start from H4 in column "H" what changes should be made in this code!

I think you just need to change the range you are applying it to.
See if this works:

Sub DelColH()
  Dim V As Variant, DeleteMe As Variant, lr As Long
  lr = Cells(Rows.Count, "H").End(xlUp).Row
  DeleteMe = Array("%", "Resistor", "Capacitor", "MCKT", "Connector")
  For Each V In DeleteMe
    Range("H4:H" & lr).Replace "*" & V & "*", "#N/A", xlWhole, , False, False, False
  Next
  On Error Resume Next
  Range("H4:H" & lr).SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
End Sub

 

[Trevor_S]

Zshanar69 - in my code that you quoted, you just need to change the 1 in the ReadRow... and For... lines to the row number of the first cell. So for H4:

ReadRow = 1
For n = 1 to LastRowNum

becomes:

ReadRow = 4
For n = 4 to LastRowNum

 

[Zshanar69]

What if I wanna start from H4 in column "H" what changes should be made in this code!

Hi Thx for reply!
Actually I was working with values & this code didn't skip one single row but it also deletes some rows I don't wanna delete so if any changes to this code would make my job perfect!

 

[Trevor_S]

Hi - please re-look at my last post, I was editing it when you replied - there are two lines which need to be changed.

 

[Zshanar69]

Zshanar69 - in my code that you quoted, you just need to change the 1 in the ReadRow... and For... lines to the row number of the first cell. So for H4:

ReadRow = 1
For n = 1 to LastRowNum

becomes:

ReadRow = 4
For n = 4 to LastRowNum

I did check n = 4 it skips 4 rows which I wanna delete!

 

[Trevor_S]

Which rows does it skip? ReadRow is the row being checked, so needs to start at 4. n is used to calculate how many rows to check. So if it's too high, some of the rows at the end won't get checked.