Finding all instances of a word and deleting that row

mellen

New Member
Joined
Apr 12, 2005
Messages
35
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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 ...
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

Active Member
Joined
May 14, 2002
Messages
420
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

New Member
Joined
Apr 12, 2005
Messages
35
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

New Member
Joined
Jul 28, 2004
Messages
44
Hello mellen

Please try this alternative

Code:
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!
 

Forum statistics

Threads
1,077,782
Messages
5,336,284
Members
399,074
Latest member
rlong98

Some videos you may like

This Week's Hot Topics

Top