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
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
Hi thanks for a speedy reply. True, but I want to keep the rows that contain computer and laptop, but at the same time I want to delete the rows that contain memory and module. How do I go about that? Is this even possible or do I need to run two macro's?
In you first if you have already establish that cell does not contain "computer" and in your second you have further established that it does not have "laptop". now it is matter of adding to see if memory or module are there and if yes then delete
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 cell has "computer", we want to skip to next cell
If InStr(strCellValue, "computer") > 0 then Goto Next_Cell
' if cell has "laptop", we want to skip to next cell
If InStr(strCellValue, "laptop") > 0 then Goto Next_Cell
' if cell has "memory " or "module" we want to delete
If ((InStr(strCellValue, "memory") > 0) or (InStr(strCellValue, "module") >0)) Then
On Error Resume Next
cell.EntireRow.Delete
On Error GoTo 0
End If
Next_Cell:
Next cell
End Sub
I think I'm almost there, but now it deletes only 7 rows so I don't think that's what I want. Let me specify again: I want the macro to look at every row and keep the rows with computer and laptop in it. Then I want the macro to look again at the remaining rows and delete the rows that have memory or module in them.
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com,http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
In the uploaded document I have two macro's not finished yet. What I want is the following:
1#: Macro first keeps all the rows with "computer", "laptop", "latitude", etc.
2#: Macro looks at the kept rows again and deletes rows that include "geheugen", "module", "lamp", etc.
Is it possible to combine what I want in one macro? Thanks again.
Thats what the above macro was supposed to do. It should have skipped all rows that have "laptop" or "computer" in cells h2 thru H1000
It should have deleted if H2 thru H1000 was memory or module
I added the macro you suggested to the file. I ran the macro on sheet 2 and it still has rows that need to be deleted, can you take a look at the macro and the output?
The reason was as the rows were deleted, rows were moved up, hence it was skipping on some rows. I missed that part
Try this
Sub Delete()
Dim lMaxRows As Long
Dim strCellValue As String
Dim RowCount As Long
lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = lMaxRows To 2 Step -1
strCellValue = Cells(RowCount, "A")
' if cell has "computer", we want to skip to next cell
If InStr(strCellValue, "computer") > 0 Then GoTo Next_RowCount
' if cell has "laptop", we want to skip to next cell
If InStr(strCellValue, "laptop") > 0 Then GoTo Next_RowCount
' if cell has "memory " or "module" we want to delete
If ((InStr(strCellValue, "memory") > 0) Or (InStr(strCellValue, "module") > 0)) Then
On Error Resume Next
Rows(RowCount).Delete
On Error GoTo 0
End If
Next_RowCount:
Next RowCount
End Sub
My last upload contains the updated macro you posted last time, but it still has rows with the word "geheugen" (see line 14 etc). Anyway maybe I should try to explain it differently. This is how I would do it manually if I had to work my way through the list.
Step 1 - Delete rows that do not contain the word "laptop"
Step 2 - Delete rows that do not contain the word "computer"
Step 3 - Delete rows that contain the word "memory"
Step 4 - Delete rows that contain the word "module"
With every step it needs to look at all the rows again.
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.
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
- Posts
-
4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020
768As 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
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- Posts
-
4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020
7681. 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