Deleting X Accounts

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,661
Office Version
  1. 2019
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,844
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 26, 2006
Messages
5,661
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,844
Office Version
  1. 365
Platform
  1. Windows
Change:
Range("A1:J65536").Delete Rows
to
Range("A1:J65536").EntireRow.Delete
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764

ADVERTISEMENT

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

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,844
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 26, 2006
Messages
5,661
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,844
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 26, 2006
Messages
5,661
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Jun 26, 2006
Messages
5,661
Office Version
  1. 2019
Platform
  1. Windows
Deleting rows containing an X

Hi JM14

Code working perfectly

Thanks


Howard
 

Watch MrExcel Video

Forum statistics

Threads
1,113,953
Messages
5,545,148
Members
410,666
Latest member
Al3cs
Top