Find/Delete macro stopped working

mswicegood

New Member
Joined
Oct 9, 2013
Messages
11
I have a macro that searches through data and finds the word TEST in column A then deletes the entire row (the test record). It worked fine for months but recently has stopped working. Nothing has changed. I cannot figure out why it is not working. Below is the code and a sample of the data.

Thanks in advance for any help!

P.S. I am using excel 2007

Code:
Sub Step_1()
'
' Step_1 Macro
'
'Deletes all TEST records
    Sheets("RawData").Select
    
    On Error Resume Next
    Dim cl As Range, c As Range
    With ActiveSheet.Range("a:a")
        Set c = .Find("TEST", LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
        Do
            Set cl = c
            Set c = .FindNext(c)
            cl.EntireRow.Delete Shift:=xlToLeft
        Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
End Sub


code
Cust
TRX_no
Date
Due Date
69
REG1
91010234
3/19/2014
3/27/2014
69
REG1
91010248
3/19/2014
3/27/2014
1
REG2
81011128
3/20/2014
3/27/2014
1
REG2
91012312
3/21/2014
3/27/2014
TEST
Test
8918543
9/13/2013
9/23/2013
TEST
Test
8921516
9/19/2013
9/27/2013
TEST
Test
8937459
########
########
TEST
Test
8937461
########
########
TEST
Test
8938711
########
11/1/2013
TEST
Test
7968354
########
1/2/2014
TEST
Test
6968550
########
1/2/2014
TEST
Test
7977427
1/14/2014
1/23/2014
TEST
Test
9983051
1/24/2014
2/3/2014
TEST
Test
8983052
1/24/2014
2/3/2014
TEST
Test
9990282
2/10/2014
2/19/2014
TEST
Test
9996079
2/21/2014
3/3/2014
TEST
Test
1004413
3/11/2014
3/19/2014

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Your TEST appears to be surrounded by spaces. Maybe

Code:
Sub Step_1()
'
' Step_1 Macro
'
'Deletes all TEST records
    Sheets("RawData").Select
    
    On Error Resume Next
    Dim cl As Range, c As Range
    With ActiveSheet.Range("a:a")
        Set c = .Find("TEST", LookIn:=xlValues, lookat:=xlPart)
        If Not c Is Nothing Then
            firstAddress = c.Address
        Do
            Set cl = c
            Set c = .FindNext(c)
            cl.EntireRow.Delete Shift:=xlToLeft
        Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
End Sub
 

mswicegood

New Member
Joined
Oct 9, 2013
Messages
11
Thank you, It works again :) Not sure why it stopped in the first place but, I will keep that trick in mind for the future.
 

mswicegood

New Member
Joined
Oct 9, 2013
Messages
11
Well, as it turns out, this still didn't fix it. I know this sounds odd but I ran it yesterday and it worked one time. Then the next time I ran it (and all subsequent times yesterday) it didn't. Then I ran it again this morning (after shutting down my machine and restarting last night) and it worked fine. I ran it again a minute ago and it didn't.

Does anyone know why a macro would sometimes work and sometimes not? Nothing in the report I get my raw data from or the sheet it is being used in has changed. THe only thing I changed yesterday was adding the LookAt:=xlPart section as suggested.

:confused:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,242
Messages
5,600,506
Members
414,385
Latest member
Lioness227

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top