Macro to delete row based on cell criteria

[MPFX2022]

Greetings all,
Ive done a search here but cant find what Im looking for.

I need a macro to search Column J, find any cells that contain the word "Full" and then delete that entire row.

Any help would be greatly appreciated

 

[Skyybot]

Try this on a copy of your Workbook.

Option Explicit

Const fnd As String = "Full"

Sub DeleteWordFull()
Dim lRow As Long, i As Long

lRow = Columns("J").Rows.End(xlDown).Row


For i = lRow To 1 Step -1
    If Range("J" & i).Value = fnd Then Rows(i).Delete
Next i

End Sub

 

[Trebor76]

Hi there,

I'm surprised you couldn't find a macro to delete the applicable row(s) as I know I have answered numerous threads asking a very similar request.

That said try this (initially on a copy of your data as the results cannot be undone if they're not as expected):

Option Explicit
Sub Macro1()

    Dim ws As Worksheet
    Dim rngCell As Range, rngDelete As Range
   
    Application.ScreenUpdating = False
   
    Set ws = ThisWorkbook.Sheets("Sheet1") 'Name of sheet where the rows are to be deleted from. Change to suit if necessary.
   
    With ws
        For Each rngCell In .Range("J2:J" & .Range("J" & Rows.Count).End(xlUp).Row)
            If Trim(StrConv(rngCell, vbLowerCase)) = "full" Then
                If rngDelete Is Nothing Then
                    Set rngDelete = rngCell
                Else
                    Set rngDelete = Union(rngDelete, rngCell)
                End If
            End If
        Next rngCell
    End With
   
    'If the 'rngDelete' range has been set, then...
    If Not rngDelete Is Nothing Then
        '...delete the row(s) from it.
        rngDelete.EntireRow.Delete
    'Else...
    Else
        '...inform the user that no rows were deleted as there were no matching criteria within the dataset.
        MsgBox "There were no rows deleted as no there were no entries with the text ""Full"".", vbExclamation
    End If
   
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[MPFX2022]

Thank you Both so much. Worked perfectly

I did search many times, perhaps the wording was wrong, But again, thank you both for your help