Delete Rows Based on Cell Value

[epoworski]

Hi there,

This is my first post, so hopefully I provide enough information and not too much. I'm also quite new to excel and VBA.

I have a workbook with about 50 worksheets. In each sheet, in column A, there is a cell with the text "CA1111". Currently, I'm manually going into each sheet, searching column A for that cell, then selecting that row and maybe 1000 rows after that one and deleting them.

Is there VBA code that will search each sheet, search for CA1111, select the row containing it, and delete that row plus the following 1000 rows?

Thanks,
Erin

 

[My Aswer Is This]

What does this mean:
You said:
then selecting that row and maybe 1000 rows after that one and deleting them.

Excel does not like "Maybe 1000 rows"

Do you mean delete the row with "CA1111" in column A and all the below rows with data in column "A"

 

[My Aswer Is This]

Try this:
If you want all rows including and below the row with "CA1111" in column "A" deleted
In all sheets in your workbook

Try this script

Sub Test()
Application.ScreenUpdating = False
'Modified 5-12-17 7:12 PM EDT
Dim i As Long
Dim Lastrow As Long
    For b = 1 To Sheets.Count
        Lastrow = Sheets(b).Cells(Rows.Count, "A").End(xlUp).Row
        For i = 1 To Lastrow
            If Sheets(b).Cells(i, 1).Value = "CA1111" Then Sheets(b).Rows(i & ":" & Lastrow).Delete: Exit For
        Next
    Next
Application.ScreenUpdating = True
End Sub

 

[Michael M]

I think this will be quicker

Sub MM1()
Dim Addr As String, ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
    ws.Activate
    Addr = ws.Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Address
    Range(Addr) = Evaluate(Replace("IF(ISNUMBER(SEARCH(""CA1111"",@)),""#N/A"",@)", "@", Addr))
    Range(Addr).SpecialCells(xlConstants, xlErrors).EntireRow.Delete
Next ws
Application.ScreenUpdating = True
End Sub

 

[footoo]

I think this will be quicker

Sub MM1()
Dim Addr As String, ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
    ws.Activate
    Addr = ws.Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Address
    Range(Addr) = Evaluate(Replace("IF(ISNUMBER(SEARCH(""CA1111"",@)),""#N/A"",@)", "@", Addr))
    Range(Addr).SpecialCells(xlConstants, xlErrors).EntireRow.Delete
Next ws
Application.ScreenUpdating = True
End Sub

But maybe not quicker than :

Sub Test()
Dim ws As Worksheet, fnd As Range
Application.ScreenUpdating = False
For Each ws In Worksheets
    Set fnd = ws.[A:A].Find("CA1111")
    If Not fnd Is Nothing Then ws.Range(fnd, ws.Cells(Rows.Count, "A").End(xlUp)).EntireRow.Delete
Next
End Sub

(Depends what the OP wants to do)

 

[My Aswer Is This]

The one thing about this script is that I sure do not understand lines of codes like this:
"Range(Addr) = Evaluate(Replace("IF(ISNUMBER(SEARCH(""CA1111"",@)),""#N/A"",@)", "@", Addr))"
I like code I can understand.

I think this will be quicker

Sub MM1()
Dim Addr As String, ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
    ws.Activate
    Addr = ws.Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Address
    Range(Addr) = Evaluate(Replace("IF(ISNUMBER(SEARCH(""CA1111"",@)),""#N/A"",@)", "@", Addr))
    Range(Addr).SpecialCells(xlConstants, xlErrors).EntireRow.Delete
Next ws
Application.ScreenUpdating = True
End Sub

 

[footoo]

The one thing about this script is that I sure do not understand lines of codes like this:
"Range(Addr) = Evaluate(Replace("IF(ISNUMBER(SEARCH(""CA1111"",@)),""#N/A"",@)", "@", Addr))"
I like code I can understand.

The following does the same (based on a similar method but using XL's Replace tool instead of a worksheet formula), and is simpler to understand :

Sub Test()
Dim ws As Worksheet, fnd As Range
Application.ScreenUpdating = False
On Error Resume Next
For Each ws In Worksheets
    ws.[A:A].Replace What:="CA11111", Replacement:="=#N/A", LookAt:=xlWhole
    ws.[A:A].SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
Next
On Error GoTo 0
End Sub