VBA Delete Entire Row if Contains Certain Text

[bigmacneb]

I've searched on here, but every code I put in gives me an error back. Data in column D, If any of the cells contains "Record Only" I need it to delete the entire row.
Thanks

 

[Rick Rothstein]

Hi, this is such a wonderful thread, whereby I have already used something from it, but could do with some help with the below.

As shown in page 1 of this thread, I wanted to remove all rows that contained any data in cells in column T, so only leaving blanks - BUT I wanted it to start from row 7 as I have blank rows above this that need to be kept.

I tried the below...but it deletes my title rows. It must be simple, but how do I get this to start from row 7?

Last = Cells(Rows.Count, "T").End(xlUp).Row
For i = Last To 7 Step -1
If (Cells(i, "T").Value) <> "" Then
Cells(i, "A").EntireRow.Delete
End If
Next i

End Sub

Make the change I show in red above.

 

[RichJW]

How I am kicking myself now.

Many thanks Rick - appreciate the help.

Richard

 

[RichJW]

Hi,

I have another question regarding the above formula...

Last = Cells(Rows.Count, "T").End(xlUp).Row
For i = Last To 7 Step -1
If (Cells(i, "T").Value) <> "" Then
Cells(i, "A").EntireRow.Delete
End If
Next I

How do I get it to delete any rows where the content of column F is equal to the content of column G? So, I only want the rows left where the content differs in columns F and G - again, only starting from row 7.
I tried adding an =IF($L7=$M7 formula, in many variants, but none did what I wanted.

Thanks,
Rich

 

[Rick Rothstein]

Hi,

I have another question regarding the above formula...

Last = Cells(Rows.Count, "T").End(xlUp).Row
For i = Last To 7 Step -1
If Cells(i, "F").Value = Cells(I, "G").Value Then
Cells(i, "A").EntireRow.Delete
End If
Next I

How do I get it to delete any rows where the content of column F is equal to the content of column G? So, I only want the rows left where the content differs in columns F and G - again, only starting from row 7.

Try it this way...

Last = Cells(Rows.Count, "T").End(xlUp).Row
For i = Last To 7 Step -1
  If Cells(i, "F").Value = Cells(i, "G").Value Then
    Rows(i).Delete
  End If
Next i

I tried adding an =IF($L7=$M7 formula, in many variants, but none did what I wanted.

VBA code is completely different than worksheet formulas, so rarely, if ever, would you be able to take a worksheet formula's construction an be able to use it in a VBA coded procedure.

 

[RichJW]

Thanks again, Rick, that worked beautifully.

You'll soon be on my Christmas card list

Regards,
Rich

 

[RichJW]

Hi again,

I have another question similar to the above ones.

Starting from row 5, I only want column J to show data between 1 and 2000 inclusive, deleting all other rows of data.

I need it to run on 2 identical tabs, yet when running the macro, it failed on one of them, highlighting the "If (Cells(i, "J").Value) <= 0 Then" row of text.

I did the macro as the following. I realise there is a better way using just the one "If", however I couldn't figure it out and thought the below would do the job.

Last = Cells(Rows.Count, "J").End(xlUp).Row
For i = Last To 5 Step -1
If (Cells(i, "J").Value) <= 0 Then
Cells(i, "A").EntireRow.Delete
End If
Next i

Last = Cells(Rows.Count, "J").End(xlUp).Row
For i = Last To 5 Step -1
If (Cells(i, "J").Value) > 2000 Then
Cells(i, "A").EntireRow.Delete
End If
Next i

Oh, and what exactly does the text "End(xlUp).Row" do in the first row above?

Thanks,
Rich

 

[RichJW]

Oh, and I'll appreciate any replies, however I won't be able to read any now until tomorrow.

Thanks again,
Rich

 

[My Aswer Is This]

Try this:
This script will work on sheets 1 and 2
You said two sheets but did not name the sheets.

Sub Test()
Application.ScreenUpdating = False
Dim i As Integer
Dim b As Integer
Dim Last As Long

    For b = 1 To 2
        With Sheets(b)
            Last = .Cells(Rows.Count, "J").End(xlUp).Row
                For i = Last To 5 Step -1
                    If .Cells(i, "J").Value <= 0 Or .Cells(i, "J").Value > 2000 Then
                        .Rows(i).Delete
                    End If
                Next i
        End With
Next
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

Last = Cells(Rows.Count, "J").End(xlUp).Row
Means look in column "J" for the first filled cell in column "J" starting from the bottom of the sheet looking upward.

 

[Rick Rothstein]

I have another question similar to the above ones.

Starting from row 5, I only want column J to show data between 1 and 2000 inclusive, deleting all other rows of data.

I need it to run on 2 identical tabs, yet when running the macro, it failed on one of them, highlighting the "If (Cells(i, "J").Value) <= 0 Then" row of text.

Here is another macro for you to try (it should be faster than the method you are attempting to use), just replace the two red example sheet names with your actual sheet names...
[table="width: 500"]
[tr]
[td]

Sub Keep1to2000()
  Dim Addr As String, WS As Worksheet
  For Each WS In Sheets(Array("[B][COLOR="#FF0000"]Sheet1[/COLOR][/B]", "[B][COLOR="#FF0000"]Sheet2[/COLOR][/B]"))
    Addr = "'" & WS.Name & "'!J5:J" & WS.Cells(Rows.Count, "J").End(xlUp).Row
    WS.Range(Addr) = Evaluate(Replace("IF((@<1)+(@>2000),""#N/A"",IF(@="""","""",@))", "@", Addr))
    On Error Resume Next
    WS.Columns("J").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
  Next
End Sub

[/td]
[/tr]
[/table]