Howdy, Stranger!

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

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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.