Excel VBA - Find all cells with value and delete the entire row if it exist

Question

This is my first time asking a question on here. I have dug through similar questions, but have had no luck yet in resolving this quandary. I appreciate any help you can give me.

In the data set I am working with, I am looking to delete any rows that contain the word "Bench" in column R. I already have the rest of the worksheet running and have the Lrow value set as the last row.

I was first successful using the .Setfilter, selecting the range, and using EntireRow.Delete. But this ended up deleting the entire dataset if there were no rows to select.

To summarize the ask: Looking in Range("R2":"R" & Lrow), find all cells containing the text "Bench", then Delete the row.

Thank you!

Here is the entire VBA as sits right now (this bit is near the bottom):

Sub BE_Time_to_Fill()
'
' BE_Time_to_Fill Macro
'

Dim StartCell As Range
Dim RangeName As String
Dim myValue As Variant


Set StartCell = Range("A1")

myValue = InputBox("Enter Date: YY-MMM")

'Select Range
  StartCell.CurrentRegion.Select
  RangeName = "Dataset"

Dim LRow As Long
Dim lCol As Long

    'Find the last non-blank cell in column A(1)
    LRow = Cells(Rows.Count, 1).End(xlUp).Row

    'Find the last non-blank cell in row 1
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column

    Columns("J:J").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("J1").FormulaR1C1 = "Time to Fill"
    Range("J2", "J" & LRow).FormulaR1C1 = "=RC[1]+RC[2]"

    Range("F1").Select
    Range("F1").FormulaR1C1 = "Job Code"
    Range("F1", "F" & LRow).AutoFilter 1, ""
    Range("F2", "F" & LRow).FormulaR1C1 = "=RC[-1]"
    [F1].AutoFilter

    Range("M1").FormulaR1C1 = "Source Time"

    Columns("N:N").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("N1").FormulaR1C1 = "Cycle Time"
    Range("N2", "N" & LRow).FormulaR1C1 = "=IMSUB(RC[1],RC[-1])"

    Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").FormulaR1C1 = "Application ID"
    Range("A2", "A" & LRow).FormulaR1C1 = "=CONCATENATE(RC[1],RC[4])"

    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B1").FormulaR1C1 = "Timeframe"
    Range("B2", "B" & LRow).Value = myValue


    Dim rng As Range
    Dim DelRng As Range

    Set DelRng = Range("R2:R" & LRow)

    For Each rng In DelRng
        If rng.Value = "*Bench" Then
            rng.EntireRow.Delete
        ElseIf rng.Value <> "*Bench" Then
        End If
    Next

    Range("G:H,M:N").Delete Shift:=xlToLeft 
    Range("A1").Select
End Sub

Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers. See: How to create a minimal reproducible example — Scott Craner
If rng.Value = "*Bench" does not operate as a wild card. Use If rng.Value Like "*Bench*" instead — chris neilsen

Mike Mann Mike Mann · Accepted Answer · 2018-04-12T21:56:14

Without seeing what code you have we can't help update it. But from your question the below might help.

If you're using a loop you'll need to include what to do if the set conditions aren't met. See example:

Sub example()
    Dim rng As Range, DelRng As Range
    Dim LastRow As Long
    LRow = Range("R" & Rows.Count).End(xlUp).Row 'test for last filled row in column R

    Set DelRng = Range("R1:R" & LRow) 'sets your range
    For Each rng In DelRng
                                                                     'change this value to match whatever you want to find. make sure this is entered as ALL CAPS and without spaces
        If UCase(WorksheetFunction.Substitute(rng.Value, " ", "")) = "GEM/BENCH" Then
            rng.EntireRow.Delete
        ElseIf UCase(WorksheetFunction.Substitute(rng.Value, " ", "")) <> "GEM/BENCH" Then 'if loop can't find anything it will just exit
        End If
    Next
End Sub

Excel VBA - Find all cells with value and delete the entire row if it exist

1 Answers