If Cell Equals "" Delete Row Macro

[shawn2209]

I am needing a macro formula for if a cell = "DELETE ME" then it will delete that row.

 

[frank_AL]

What Column would "Delete Me" be in and do you want this to look at all rows on a single worksheet or all worksheets in a workbook?

 

[shawn2209]

"DELETE ME" would be in the L Column of Sheet "Overage Tracking Running Report"

 

[Trebor76]

Hi there,

Here's one way for smaller datasets (run it while on the "Overage Tracking Running Report" sheet):

Option Explicit
'Option Compare Text 'Ignores case sensitivity
Sub Macro2()

    Dim lngMyRow As Long
    
    Application.ScreenUpdating = False
    
    For lngMyRow = Cells(Rows.Count, "L").End(xlUp).Row To 2 Step -1 'Works backwards up Col. L to Row 2. Change to suit if necessary.
        If Range("L" & lngMyRow) = "DELETE ME" Then 'Case sensitive.  If you don't want case sensitivity, comment out the line of code immediately under Option Explicit
            Rows(lngMyRow).EntireRow.Delete
        End If
    Next lngMyRow
    
    Application.ScreenUpdating = True

End Sub

Just make sure to initially run it on a copy of your data as the results cannot be undone if they're not as expected.

Regards,

Robert

 

[shawn2209]

So I have the formula that says "DELETE ME" in worksheet "Overage Tracking Running Report" will it go recognize that sheet or will it go through the whole workbook to find "DELETE ME" and clear that row?

 

[shawn2209]

Ok and sorry, I just though of this. If I delete the whole row then it will erase the formula in L for future additions to that sheet. Can I get a macro that clear contents just from A:F on that row?

 

[Trebor76]

Ok and sorry, I just though of this. If I delete the whole row then it will erase the formula in L for future additions to that sheet. Can I get a macro that clear contents just from A:F on that row?

Try this while you're on the "Overage Tracking Running Report" tab:

Option Explicit
'Option Compare Text 'Ignores case sensitivity
Sub Macro2()

    Dim lngMyRow As Long
    
    Application.ScreenUpdating = False
    
    For lngMyRow = Cells(Rows.Count, "L").End(xlUp).Row To 2 Step -1 'Worksbackwards up Col. L to Row 2. Change to suit if necessary.
        If Range("L" & lngMyRow) = "DELETE ME" Then 'Case sensitive.  If you don't want case sensitivity, comment out the line of code immediately under Option Explicit
            Range("A" & lngMyRow & ":F" & lngMyRow).ClearContents
        End If
    Next lngMyRow
    
    Application.ScreenUpdating = True

End Sub

Robert

 

[shawn2209]

I am wanting to assign this macro to a button on another sheet. Will this still work?

 

[Trebor76]

I am wanting to assign this macro to a button on another sheet. Will this still work?

No. This should (you'll need to test it on a copy of the workbook):

Option Explicit
'Option Compare Text 'Ignores case sensitivity
Sub Macro2()

    Dim wsMyTab  As Worksheet
    Dim lngMyRow As Long
    
    Application.ScreenUpdating = False
    
    Set wsMyTab = Sheets("Overage Tracking Running Report")
    
    For lngMyRow = wsMyTab.Cells(Rows.Count, "L").End(xlUp).Row To 2 Step -1 'Worksbackwards up Col. L to Row 2. Change to suit if necessary.
        If wsMyTab.Range("L" & lngMyRow) = "DELETE ME" Then 'Case sensitive.  If you don't want case sensitivity, comment out the line of code immediately under Option Explicit
            wsMyTab.Range("A" & lngMyRow & ":F" & lngMyRow).ClearContents
        End If
    Next lngMyRow
    
    Set wsMyTab = Nothing
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[shawn2209]

I apologize if I am asking questions that seems like I should already know. I am new to the macro game and I will accept any advice if I am asking the wrong questions. Let me see if more information would help you help me. I am working between two tabs or maybe the excel term is work sheet within the work book. On " Overage Tracking Running Report" in L column I have formula =IF(AND(B1='Look up'!A13,D1='Look up'!C13,E1='Look up'!D13,F1='Look up'!E13),"DELETE ME",""). On the tab "Look up" I want to push a button to when the information is there for "DELETE ME" to show up in column L then it would clear contents in A:F. If there is more information you are needing the please let me know. I really do appreciate the help that you are giving me right now to complete this.