-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Delete row if Column H contains any of the following values - VBA
- Thread starter CC268
- Start date
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:
Code:
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
Last edited:
Upvote
0
Exact match, but it can be anywhere within the string of text in the cell...doesn't have to be like the first word or anything.
Upvote
0
Amended code is then:
This macro is not case sensitive. If you want it to be case sensitive, remove the Option Compare Text line.
It will also find/delete where the text is part of a longer word - e.g. "connector" will find and delete "connectors".
Code:
Sub DelColH()
Option Compare Text
LastRowNum = Cells.SpecialCells(xlCellTypeLastCell).Row
ReadRow = 1
For n = 1 to LastRowNum
If Range("H" & ReadRow).Value Like "*%*" = True Or _
Range("H" & ReadRow).Value Like "*Resistor*" = True Or _
Range("H" & ReadRow).Value Like "*Capacitor*" = True Or _
Range("H" & ReadRow).Value Like "*MCKT*" = True 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 Like "*Connector*" = True Then
Range("H" & ReadRow).EntireRow.Delete
Else
ReadRow = ReadRow + 1
End If
Next
End SubIt will also find/delete where the text is part of a longer word - e.g. "connector" will find and delete "connectors".
Upvote
0
I haven't ran this yet, but an issue I am seeing is that if it goes from the first row down...when it deletes a row such as row 10, row 11 becomes row 10...so it is going to be skipping rows with this method won't it?
EDIT: just ran - it appears to work I am just worried it might be skipping lines...regardless it seemed to capture all the above words and delete the row...
Last edited:
Upvote
0
Unfortunately I am not real familiar how that might look
Upvote
0
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.
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.
Last edited:
Upvote
0
Rick Rothstein
MrExcel MVP
- Joined
- Apr 18, 2011
- Messages
- 38,462
- Office Version
- 365
- Platform
- Windows
Here is another macro that you can consider (add any new items to the comma delimited quoted list that I show in blue below)...
Rich (BB code):
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, , True, False, False
Next
On Error Resume Next
Columns("H").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
On Error GoTo 0
End Sub
Last edited:
Upvote
0
Thanks! Would you mind commenting on your code from "From Each V In DeleteMe" on down? I just like to understand what exactly is going on in the code.
Last edited:
Upvote
0
