Delete row if it contains certain text with VBA

[Dokat]

Hi

I have a worksheet ("Region") with over 300K rows of data . I'd like to delete entire rows where intersecting column F value is "Total USA" with VBA.

For Ex: Delete row 4 if F4 is "Total USA".

I have below code however its giving me Runtime error 424. Appreciate any help modifying code. Thanks

Sub RowTotalUSA()
  Dim i As Long
 
  Application.ScreenUpdating = False
  With Sheets("Region")
    For i = .UsedRange.Row.Count To 1 Step -1
      If .Cells(Column.Count, i).End(xlUp).Column = "Total USA" Then .Row(i).Delete
    Next i
  End With
  Application.ScreenUpdating = True
End Sub

 

[bebo021999]

This will loop through column F, then collect row index where "Total USD" is found, then delete entire row at one:

Option Explicit
Sub test()
Application.ScreenUpdating = False
Dim Lr&, i&, st As String
With Sheets("Region")
    Lr = .Cells(Rows.Count, "F").End(xlUp).Row
    For i = 1 To Lr
        If Cells(i, "F").Value Like "Total USA" Then
            st = st & "," & i & ":" & i ' collect rows index
        End If
    Next
    st = Mid(st, 2, 255)
.Range(st).Delete
End With
Application.ScreenUpdating = True
End Sub

 

[Dokat]

Thank you for your reply.

This will loop through column F, then collect row index where "Total USD" is found, then delete entire row at one:

Option Explicit
Sub test()
Application.ScreenUpdating = False
Dim Lr&, i&, st As String
With Sheets("Region")
    Lr = .Cells(Rows.Count, "F").End(xlUp).Row
    For i = 1 To Lr
        If Cells(i, "F").Value Like "Total USA" Then
            st = st & "," & i & ":" & i ' collect rows index
        End If
    Next
    st = Mid(st, 2, 255)
.Range(st).Delete
End With
Application.ScreenUpdating = True
End Sub

When i tried thecode i received below error message. JoeMOe's code worked earlier i am all set. Thank you

 

[Dokat]

lrow: Worksheets("Region").Range("F" & Rows.Count).End(xlUp).Row

equates to:

lrow
Worksheets("Region").Range("F" & Rows.Count).End(xlUp).Row

Also:

If Cells(iCntr, 1) = "Total USA" Then

The 1 in that line refers to column 1 which is the A column. Remember I said cells uses a Row,Column format.

Then should i replace cells with row?

 

[johnnyL]

You would replace the '1' with '6', signifying the the 6th column which = F.

 

[Dokat]

You would replace the '1' with '6', signifying the the 6th column which = F.

I tried that too however received error message. JoeMoe's code worked earlier. I am all set. Thanks for your help.

 

[johnnyL]

I don't see how you can get that error if you have a sheet named Region in the same workbook.

Sub DeleteUSA()

Dim lrow As Long
Dim icntr As Long

lrow = Worksheets("Region").Range("F" & Rows.Count).End(xlUp).Row


For icntr = lrow To 1 Step -1

If Cells(icntr, 6) = "Total USA" Then
         
        Rows(icntr).Delete
   
    End If

Next

End Sub

Maybe you want to use 'ActiveSheet' ?

 

[Dokat]

I don't see how you can get that error if you have a sheet named Region in the same workbook.

Sub DeleteUSA()

Dim lrow As Long
Dim icntr As Long

lrow = Worksheets("Region").Range("F" & Rows.Count).End(xlUp).Row


For icntr = lrow To 1 Step -1

If Cells(icntr, 6) = "Total USA" Then
        
        Rows(icntr).Delete
  
    End If

Next

End Sub

Maybe you want to use 'ActiveSheet' ?

I am nit sure why. Please see below screenshot.

 

[johnnyL]

Exactly! You don't have a sheet named Region.

Change the Sheet1 name to Region ... Or change the Region in the codes to Sheet1.

 

[Dokat]

Exactly! You don't have a sheet named Region.

Change the Sheet1 name to Region ... Or change the Region in the codes to Sheet1.

I do. Please see below. I circled it in yellow

 

[johnnyL]

Try the following:

Sub DeleteUSA()

Dim lrow As Long
Dim icntr As Long

lrow = ActiveSheet.Range("F" & Rows.Count).End(xlUp).Row


For icntr = lrow To 1 Step -1

If Cells(icntr, 6) = "Total USA" Then
          
        Rows(icntr).Delete
    
    End If

Next

End Sub