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

 

[johnnyL]

You are mixing up your Rows and columns, among other things.

The 'Cells' uses a Row, Column format. So when you increment 'i', you are incrementing the column.
The 'i' increment you set up is for rows.

etc.

 

[Dokat]

You are mixing up your Rows and columns, among other things.

The 'Cells' uses a Row, Column format. So when you increment 'i', you are incrementing the column.
The 'i' increment you set up is for rows.

etc.

Thanks for your reply. I noticed that i modified the code as below but still getting Run time error 438. Please see below.

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, 1) = "Total USA" Then
          
        Rows(iCntr).Delete
    
    End If

Next

 

[Akuini]

@Dokat
With 300K+ rows, your method will take long to finish. It would be faster if you could sort the data first.
Is it allowed to sort your data by col F?
If yes then I could write a macro to:

1. sort data by col F
2. find the first & last row with "Total USA" in col F
3. remove both rows & all rows in between at once

 

[johnnyL]

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.

 

[JoeMo]

If your column F cells are constants, not formulas, try this:

Sub RemoveRowTotalUSA()
'Assumes filled cells in col F are constants not formulas
Dim R As Range
Set R = Intersect(ActiveSheet.UsedRange, Range("F:F"))
Application.ScreenUpdating = False
R.Replace what:="Total USA", replacement:="#N/A", lookat:=xlWhole
On Error Resume Next
R.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

 

[Dokat]

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

equates to:

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

I corrected that type replaced : with = now i am not receiving error message however code is not doing anything

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, 1) = "Total USA" Then
          
        Rows(iCntr).Delete
    
    End If

Next

End Sub

 

[Dokat]

If your column F cells are constants, not formulas, try this:

Sub RemoveRowTotalUSA()
'Assumes filled cells in col F are constants not formulas
Dim R As Range
Set R = Intersect(ActiveSheet.UsedRange, Range("F:F"))
Application.ScreenUpdating = False
R.Replace what:="Total USA", replacement:="#N/A", lookat:=xlWhole
On Error Resume Next
R.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Thank you. It worked smoothly.

 

[johnnyL]

Sorry, I edited my last post to add additional info, please reread it.

 

[JoeMo]

Thank you. It worked smoothly.

You are welcome - thanks for the reply.