'Delete entire ROW if a cell contains a phrase' Thread RSS Feed

Delete entire ROW if a cell contains a phrase

Mon, 01 Dec 2008 10:53:56 -0700

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!

Re: Delete entire ROW if a cell contains a phrase

Mon, 01 Dec 2008 23:29:50 -0700

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

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

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 (http://vbadud.blogspot.com)

C# Code Snippets (http:dotnetdud.blogspot.com)

Re: Delete entire ROW if a cell contains a phrase

Mon, 29 Oct 2012 06:41:47 -0700

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

Re: Delete entire ROW if a cell contains a phrase

Mon, 29 Oct 2012 06:43:39 -0700