Excel Macro - Delete Row if cell contains - Programmers Heaven

Howdy, Stranger!

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

Categories

Excel Macro - Delete Row if cell contains

pizzabotpizzabot Posts: 1Member
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

  • atunaatuna Posts: 1Member
    : 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.



  • atezzaatezza Posts: 1Member
    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.