Code to delete all rows if cell equals MyText only deletes first row

[Tim Hanson]

I can't figure out how to get my code to delete`all` rows with cells values of `privite` in column `F`

Only the first insurance is deleted

Thanks

    Sub RemovePrivate()
    Dim rng As Range, cell As Range
    Dim lRow As Long
    Dim ws As Worksheet

    Set ws = Sheets(1)
    lRow = ws.Range("A" & ws.Rows.count).End(xlUp).Row
   
    Set rng = ws.Range("F1:F" & lRow)
    If Not rng Is Nothing Then
        For Each cell In rng.Cells
          If cell.Text = "private" Then
            cell.EntireRow.Delete
          End If
        Next
      End If
    End Sub

 

[iferror]

Hi Tim,
I prefer the filter and delete option rather than the loop throught the whole range.
So basically i filter the "Private" and Delete the rows

If you want to test this, de-comment the .Select and comment the 'Delete (and you might well do this...as i did not have time to test )

Sub RemovePrivate()

Application.DisplayAlerts = False

Dim ws As Worksheet
Set ws = Sheets(1)

Dim lRow As Long
lRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

With ws
  .AutoFilterMode = False
  .Range("A1:F" & lRow).AutoFilter
  .Range("A1:F" & lRow).AutoFilter Field:=6, Criteria1:="Private"
  '.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Select
   .UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete

End With

Application.DisplayAlerts = True

End Sub

 

[Tim Hanson]

Thank you iferror, that worked great, never seen that approach before, I am going to go rewrite some of my macros in this style.

 

[Debraj]

Hi Tim,

Here is a method, for deleting rows from TOP side..
Just for playing..

Sub RemovePrivate()
 Dim rng As Range, cell As Range, oldselection As Range
 Dim lRow As Long
 Dim ws As Worksheet

 Set ws = Sheets(1)
 lRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

 Set rng = ws.Range("F1:F" & lRow)
 If Not rng Is Nothing Then
     For Each cell In rng.Cells
       If cell.Text = "Private" Then
        If oldselection Is Nothing Then
            Set oldselection = cell
        Else
            Set oldselection = Application.Union(cell, oldselection)
        End If
       End If
     Next
   End If
   oldselection.EntireRow.Delete
 End Sub

 

[Tim Hanson]

Debraj. Thank you, I will play with this, also I never noticed the like button I am going go back to my old threads and like the answers, thanks for that as well.