Deleting X Accounts

[howard]

I have a spreadsheet with three columns of data. The data in column A has various account numbers. I need to delete all the account numbers ending in an X.

I have set up the following VBA code, but cannot get it to work. Is there anyone who can assist

Sub ClearX()

With Columns("A:C")
.AutoFilter Field1:=1, Criteria:="=X*"
End With
Range("A1:C65536").ClearContents
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True

End Sub

Your assistance will be most appreciated

Howard

 

[Joe4]

If you want all values ending in an X, then instead of:
Criteria:="=X*"
your criteria should be:
Criteria:="*X"


(I haven't checked for other errors, just pointed out this first one I saw. You can get a lot of the code you need by using the Macro Recorder).

 

[howard]

Deleting rows containing an X

Thanks for the response

Please find below code that I have used to delete rows containing an account ending in an X for eg 6X, 301X, 4001X

The code comes up with a "Compile E Sub or Function not defined"
error

It would be appreciated if you would test my code and correct


Sub Clear_X_Accounts()
    
        Application.ScreenUpdating = False
    With Columns("A:J")
        .AutoFilter Field:=1, Criteria1:="*X*"
   
    End With
    Range("A1:J65536").Delete Rows
    ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True

End Sub

Howard

 

[Joe4]

Change:
Range("A1:J65536").Delete Rows
to
Range("A1:J65536").EntireRow.Delete

 

[njimack]

Shouldn't the deletion only apply to the visible rows?

Range("A1", Range("A1").End(xlDown)).SpecialCells(xlCellTypeVisible).EntireRow.Delete

 

[Joe4]

Neil,

Give his code a try (with the fix). Since it hides all the other rows via the Filter, it DOES only delete the rows he is targetting.

 

[howard]

Hi JM14

When running the macro to delete numbers ending in a X for eg 301X,6X,4001X etc, the code below does not work.

It would be appreciated if you would check-See Sample speadshhet below

11X MANAGERS-BRANCH 11
12X NV MAZ MANAGERS 22
3001X UNITS SOLD MARATHON 0
6X Contra A/c -33
2216B VAT INPUT SPARE (A) 0
2216S VAT OUTPUT SPARE (A) 0
2217B VAT INPUT SPARE (B) 0
2217S VAT OUTPUT SPARE (B) 0
2218B VAT INPUT EXEMPT 0
2218S VAT OUTPUT EXEMPT 0

Sub Clear_X_Accounts()
          Application.ScreenUpdating = False
 With Columns("A:J")
.AutoFilter Field:=1, Criteria1:="*X*"
     End With

    Range("A1:J65536").EntireRow.Delete

    ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True

End Sub


Your assistance will be most appreciated


Howard

 

[Joe4]

I ran it and it seems to work fine for me.

How is it not working? What is it doing when you try it?

 

[howard]

Hi NJIMACK

Thanks for your help.

The deletion must only apply to Visible cells ending in an X

See Sample below
17X CLERICAL-N/VS 61
18X CLERICAL-U/VS 10
19X CLERICAL-PARTS 22
20X CLERICAL-SERVICE 72
2200A VEH F/PLAN FACTORY -1308828.64
2200B VEH F/PLAN BANKFIN -31277227.07
2200S SERV SUBLET CLRG A/C 43428.81
2215B VAT INPUT STD 0
2215S VAT OUTPUT STD 0
2216B VAT INPUT SPARE (A) 0
2216S VAT OUTPUT SPARE (A) 0
2217B VAT INPUT SPARE (B) 0
2217S VAT OUTPUT SPARE (B) 0
2218B VAT INPUT EXEMPT 0
2218S VAT OUTPUT EXEMPT 0
2219B VAT INPUT ZERO RATE 0
2219S VAT OUTPUT ZERO RATE 0
2219V VAT CLEARING A/C -550200.71
2243E PROV FREE SERV-EXT 0
2245F PROV NV ADVERTS FRD 0
2263B DEFERRED COY TAX 592068.2
2270A O/BALS CLEARING N/V 0
2270B INTER DEPT TRF CLRNG 0
2270D O/BALS CLRG PARTS 0
2275A B/DEBTS SPEC N/VS -33465.19
2275B B/DEBTS SPEC U/V -22246.49
2275C B/DEBTS SPEC SERVICE -97353.5
2275D B/DEBTS SPEC PARTS -69543.59
2276A B/DEBTS GEN N/VS -7800
2276B B/DEBTS GEN U/VS -500
2276C B/DEBTS GEN SERVICE -260777
2276D B/DEBTS GEN PARTS -53600
2291F ACC DEPR DEMO FORD -619288
2291M ACC DEPR DEMO MAZDA -167712
22X CLERICAL-ADMIN 47
23X SUPERVISORS-SERVICE 12
24X TECHNICIANS-SERVICE 98
25X APPRENTICES-SERVICE 47
26X RSA'S-SERVICE 8
27X NV MAZ SALESMEN 122
28X SALESMEN-U/VS 64
29X SALESMEN-PARTS 27
3000D D/C RET MAZDA 323 0
3000K FACT INCS MAZDA 323 0
3000X UNITS SOLD MAZDA 323 0
3001D D/C RET MARATHON 0
3001K FACT INCS MARATHON 0
3001X UNITS SOLD MARATHON 0
3002D D/C RET MAZDA 626 0


Only the rows where the accounts ending in an X to be deleted

Howard

 

[howard]

Deleting rows containing an X

Hi JM14

Code working perfectly

Thanks


Howard