Finding all instances of a word and deleting that row

[mellen]

I've got a file that has lots of data over several categories, and a sub total after each category. I want to delete the rows that have this sub total in it, just want the file to be flat.

I've got it to work for a particular file that has 2 categories, but I want to use a Do While (or For loop), cos some of the other files have >2 categories, so hard coding the rows to delete doesn't exactly work

The word that is common (and in my current find) is "Total". This is what I have so far, any ideas on what I should wrap this in?

Thanks in advance

Dim r As Integer
Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
r = ActiveCell.Row
Rows(r).Select
Selection.Delete Shift:=xlUp

Mellen

 

[Yogi Anand]

Hi Mellen:

Your code snipette deletes the cell containing 'total' ... it does not delete the related row. So if you want to delete just the cell containing 'total', then you can use the following code ...

Sub y_1()
    Dim r As Integer
    
    Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
     :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
     False).Delete Shift:=xlUp  'Activate
    
    r = ActiveCell.Row
    'Rows(r).Select
    'Selection.Delete Shift:=xlUp
    
    Cells.FindNext.Delete Shift:=xlUp
    
    If ActiveCell.Row = r Then Exit Sub

End Sub

If you need to delete the related row, then modify the code accordingly.

 

[jag108]

Does the word Total always appear in the same column?

If so try this.

Sub test()
Set TestRange = Intersect(Range("B:B"), ActiveSheet.UsedRange)
lastrow = TestRange.Cells(TestRange.Cells.Count).Row 'get last row number in the range
firstrow = TestRange.Cells(1).Row 'get the first row number in the range
For I = lastrow To firstrow Step -1
cell_var = UCase(Cells(I, 3).Value) ' The column needs to be adjusted to your column
If cell_var = "TOTAL" Then
Cells(I, 3).EntireRow.Delete
End If
Next I
End Sub

Just adjust the column number to your TOTAL column.

 

[mellen]

Hi jag, unfortunately, Total isn't the only word in that cell...

Hi Yogi, I tried my code again to check, and it seems to delete the row as desired? (just can't get it to do all of them).

I also tried yours and it deletes the cell as you have said, but doesn't seem to delete every instance that Total exists.

Just to clarify, I am trying to delete the entire row that has it contained.

Cheers,

Len

 

[Fausto]

Hello mellen

Please try this alternative

Sub deltotal()
Application.ScreenUpdating = False
On Error Resume Next
Do
Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
If Err <> 0 Then Exit Do
Let a = ActiveCell.Row
Rows(a).Delete
Loop
Range("a1").Activate
Application.ScreenUpdating = True
End Sub

It has worked for me when I tested it, please let me know if this works for you!

 

[mellen]

Fausto,

Perfect, just what I was after!

Cheers,

Mellen