Previous Topic Next Topic

Deleting and keeping rows in same Macro

Closed
Jess - Jul 26, 2010 at 08:03 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jul 28, 2010 at 09:21 AM
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

Answer 1 / 1
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Jul 26, 2010 at 08:18 AM
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
0
Jess
Jul 26, 2010 at 08:53 AM
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?
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Jul 26, 2010 at 09:26 AM
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
0
Jess
Jul 26, 2010 at 03:29 PM
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.
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Jul 26, 2010 at 07:42 PM
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
0
Jess
Jul 27, 2010 at 02:23 AM
https://authentification.site/files/23540796/Book1.xls

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.
0