Howdy, Stranger!

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

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
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.

Delete entire ROW if a cell contains a phrase

heweaverheweaver Posts: 1Member
I am trying to write a small piece of code for an Excel macro. I am working with some ladies here who use an excel spreadsheet to export some of the numbers from their system to their customer.

I need a quick macro that can examine each line of the .xls file and if a certain phrase appears in a cell (for instance, any cell in column C) then have it delete that entire line.

Additionally, if another macro (or maybe even an extension of the first macro, but it may be easier to have two separate macros) that could look at another row, like row FIVE, and if it contains all zeros for cells D-AA (or whatever it goes to) then I would like to have it remove that entire row if it has nothing but zeros from a certain cell over.

Any help would be greatly appreciated. I haven't had the privilege to work much with Excel with regards to creating macros and such.

Thanks in advance!

Comments

  • shasurshasur Posts: 23Member
    Hi

    Here is a simple one that checks for certain text (Beauty) in column C of the current sheet and deletes the entire row if it is found

    [code]Sub DeleteRow()
    Dim i1 As Long
    Dim iMax As Long
    Dim sText As String

    sText = "Beauty"
    sText = LCase(sText)
    iMax = Cells.SpecialCells(xlCellTypeLastCell).Row

    For i1 = iMax To 1 Step -1
    If InStr(1, LCase(Cells(i1, 3)), sText) <> 0 Then
    Rows(i1).EntireRow.Delete
    End If
    Next i1


    End Sub[/code]

    Cheers
    Shasur

    : I am trying to write a small piece of code for an Excel macro. I am
    : working with some ladies here who use an excel spreadsheet to export
    : some of the numbers from their system to their customer.
    :
    : I need a quick macro that can examine each line of the .xls file and
    : if a certain phrase appears in a cell (for instance, any cell in
    : column C) then have it delete that entire line.
    :
    : Additionally, if another macro (or maybe even an extension of the
    : first macro, but it may be easier to have two separate macros) that
    : could look at another row, like row FIVE, and if it contains all
    : zeros for cells D-AA (or whatever it goes to) then I would like to
    : have it remove that entire row if it has nothing but zeros from a
    : certain cell over.
    :
    : Any help would be greatly appreciated. I haven't had the privilege
    : to work much with Excel with regards to creating macros and such.
    :
    : Thanks in advance!
    :

    VBA Tips & Tricks ([link=http://vbadud.blogspot.com]http://vbadud.blogspot.com[/link])

    C# Code Snippets ([link=http:dotnetdud.blogspot.com]http:dotnetdud.blogspot.com[/link])
  • slongslong Posts: 5Member
    Hello,
    I tried this example in my own macro, but I (unsuccessfully) tried to convert to just clear the contents of the cell if certain (parts of) words appeared. Can you tell me what I did wrong?

    Dim i1 As Long
    Dim iMax As Long
    Dim sText As String

    sText = "cipan" Or "ce Ty"
    sText = LCase(sText)
    iMax = Cells.SpecialCells(xlCellTypeLastCell).cell

    For i1 = iMax To 1 Step -1
    If InStr(1, LCase(Cells(i1, 3)), sText) <> 0 Then
    Cells(i1).ClearContents
    End If
    Next i1

  • slongslong Posts: 5Member
    Hello,
    I tried this example in my own macro, but I (unsuccessfully) tried to convert to just clear the contents of the cell if certain (parts of) words appeared. Can you tell me what I did wrong?

    Dim i1 As Long
    Dim iMax As Long
    Dim sText As String

    sText = "cipan" Or "ce Ty"
    sText = LCase(sText)
    iMax = Cells.SpecialCells(xlCellTypeLastCell).cell

    For i1 = iMax To 1 Step -1
    If InStr(1, LCase(Cells(i1, 3)), sText) <> 0 Then
    Cells(i1).ClearContents
    End If
    Next i1

Sign In or Register to comment.