Deleting and keeping rows in same Macro

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hi,

I'm working on a Macro to keep rows if it meets two conditions, see below. Then I want the macro to look at all the rows again and delete the ones that have the text memory or module in them. How can I get that to work?


Sub Delete()
Dim rng As Range, cell As Range, del As Range
Dim strCellValue As String
Set rng = Intersect(Range("H2:H1000"), ActiveSheet.UsedRange)
For Each cell In rng

strCellValue = (cell.Value)
If InStr(strCellValue, "computer") = False Then
If InStr(strCellValue, "laptop") = False Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
End If
Next cell
On Error Resume Next
del.EntireRow.Delete
End Sub

Thanks

Jess

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Why you want to visit that cell again. If you know you have to delete the row, then delete it then. Some thing like this

Sub Delete()
Dim rng As Range, cell As Range
Dim strCellValue As String

    Set rng = Intersect(Range("H2:H1000"), ActiveSheet.UsedRange)
    For Each cell In rng
    
        strCellValue = (cell.Value)
        
        If InStr(strCellValue, "computer") = False Then
            If InStr(strCellValue, "laptop") = False Then
                On Error Resume Next
                cell.EntireRow.Delete
                On Error GoTo 0
            End If
        End If
    Next cell

End Sub
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Your last update does not appear to have the updated macro. I can only see the old one in it.

As for your steps, either I have been misreading your question or some thing else is up. Let me explain to you how

Step 1 - Delete rows that do not contain the word "laptop"
This would mean that If a row did not had a laptop, it is gone. So a row that did not had laptop and may had "module", "memory" and "computer" are being deleted


Step 2 - Delete rows that do not contain the word "computer"
Now you are planning to delete a row that did not had computer. Only rows that are left are the one that did not had laptop at the point. So now of the smaller set you are now deleting those line that only had laptop only

This does not jibe with what you were asking before
Mmm it's hard to explain on here how I want it. Let me try again if you don't mind:

Step 1 - Delete rows that do not contain the words "laptop" and "computer" (other way around would be to keep all rows that contain the words "laptop" and "computer").

Step 2 - The rows that are "visible" are only the rows that contain the words "laptop" and "computer", now here's the trick. I want these rows to be looked at again cause when a row contains "laptop" AND "memory" or "module" it needs to be deleted.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
So basically you want to keep a row if it only contains "laptop" or/and "computer" but does not contain "module" and/or "memory" ?
Yeah that's right.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
This will delete any row that
1. Does not have laptop/computer
2. has laptop/computer but it appears with memory/module

Sub Delete()
Dim lMaxRows As Long
Dim strCellValue As String
Dim RowCount As Long
Dim bLaptopComputerAbsent As Boolean

    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
     
    For RowCount = lMaxRows To 2 Step -1
     
        bLaptopComputerAbsent = True
        
        strCellValue = Cells(RowCount, "A")
         
        ' if cell has "computer", we want to skip to next cell
        If InStr(strCellValue, "computer") > 0 Then bLaptopComputerAbsent = False

        ' if cell has "laptop", we want to skip to next cell
        If InStr(strCellValue, "laptop") > 0 Then bLaptopComputerAbsent = False

        ' if cell has "memory " or "module" we want to delete
        If ((InStr(strCellValue, "memory") > 0) Or (InStr(strCellValue, "module") > 0) Or (bLaptopComputerAbsent = True)) Then
                On Error Resume Next
                Rows(RowCount).Delete
                On Error GoTo 0
         End If

Next_RowCount:
    Next RowCount

End Sub