VBA: Delete complete rows with specific value

[MJAY__77]

Hello all together,

I've got an Excel-Sheet with a lot of work sheets, having all the same structure.

Now I want to write a macro in VBA, to delete all rows which have the value "820" in the column "G". And this for every worksheet.
Do you have any suggestions?

I'm completely new in this topic, I didn't create the Excel-file but I have now the task to optimize the file.
I hope you can help me and thanks in advance.

Jonas Mayer

 

[bobsan42]

Is 820 the complete contents of the cell or just a part of it?
Is it in the value or in the formula? Do you want to completely delete the rows or just clear the contents?

maybe something like this:

Sub deleterows()
    Dim SearchFor As String: SearchFor = "820"
    Dim rng1 As Range, wsh As Worksheet, cc As Range
    For Each wsh In ThisWorkbook.Worksheets
        With wsh
            Set rng1 = Intersect(.UsedRange, .Range("G:G"))
        End With
        If Not rng1 Is Nothing Then
            For Each cc In rng1
                If InStr(1, cc.Value, SearchFor, vbTextCompare) > 0 Then cc.EntireRow.Delete
            Next cc
        End If
    Next wsh
    Set cc = Nothing
    Set rng1 = Nothing
    Set wsh = Nothing
End Sub

backup your file before you try it

 

[MJAY__77]

Hello Bobsan,

thank you very much for your answer.

820 is the complete content of the cell and it's a value, no formula.
And I want to completely delete the rows.

I used your code and it worked well. But I only used a simple Excel-file, now I'll try it in my big file.

Have I to adapt something in the code now, with the "new information" above?

 

[MJAY__77]

Hey Bobsan,

the code works in general very well.

The only issue is that the macro don't delete all rows with 820 in column G.
That means, I have to use the macro 2 or 3 times that all rows with 820 in G will be deleted.

What's the problem there?

 

[bobsan42]

problem is probably deleting while in the process of looping give me a minute or two.

 

[bobsan42]

try it like this:

Sub deleterows2()
    Dim SearchFor As String: SearchFor = "820"
    Dim rng1 As Range, wsh As Worksheet, cc As Range, rng2 As Range
    For Each wsh In ThisWorkbook.Worksheets
        Set rng2 = Nothing
        With wsh
            Set rng1 = Intersect(.UsedRange, .Range("G:G"))
        End With
        If Not rng1 Is Nothing Then
            For Each cc In rng1
                If cc.Value = SearchFor Then
                    If Not rng2 Is Nothing Then
                        Set rng2 = Union(rng2, cc)
                    Else
                        Set rng2 = cc
                    End If
                End If
            Next cc
        End If
        If Not rng2 Is Nothing Then rng2.EntireRow.Delete
    Next wsh
    Set cc = Nothing
    Set rng1 = Nothing
    Set rng2 = Nothing
    Set wsh = Nothing
End Sub

 

[MJAY__77]

Now it works perfect.

Thank you very much Bobsan for your help! I appreciate it!

Have a good day