Excel Macro - Delete Row if cell contains

I'm trying to write an excel macro that will delete a row if the active cell in column B does not contain "21475". The follow is what I have written but when it's ran, it deletes all the rows. I would appreciate any and all help. Thanks in advance.

[code]
Sub Delete_Rows_ColB()
' This macro deletes all rows on the active worksheet
' that do not have 21475 in column B.
Dim rng As Range, cell As Range, del As Range
Dim strCellValue As String
Set rng = Intersect(Range("B:B"), ActiveSheet.UsedRange)
For Each cell In rng

strCellValue = (cell.Value)
If InStr(strSiteLink, "21475") = 0 Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.Delete
End Sub
[/code]

Comments

  • : I'm trying to write an excel macro that will delete a row if the
    : active cell in column B does not contain "21475". The follow is what
    : I have written but when it's ran, it deletes all the rows. I would
    : appreciate any and all help. Thanks in advance.
    :
    : [code]:
    : Sub Delete_Rows_ColB()
    : ' This macro deletes all rows on the active worksheet
    : ' that do not have 21475 in column B.
    : Dim rng As Range, cell As Range, del As Range
    : Dim strCellValue As String
    : Set rng = Intersect(Range("B:B"), ActiveSheet.UsedRange)
    : For Each cell In rng
    :
    : strCellValue = (cell.Value)
    : If InStr(strSiteLink, "21475") = 0 Then
    : If del Is Nothing Then
    : Set del = cell
    : Else: Set del = Union(del, cell)
    : End If
    : End If
    : Next cell
    : On Error Resume Next
    : del.EntireRow.Delete
    : End Sub
    : [/code]:

    if you replace this row
    If InStr(strSiteLink, "21475") = 0 Then

    with this
    If InStr(strCellValue , "21475") > 0 Then

    it works.



  • Then how to delete a row if the active cell in column B does not contain "21475" in every worksheet?
    Thanks in advance
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories