macro

[howard]

i have written code to delete all rows containing the following in col A from from 2 onwards

~
text containing Copy


I get a run time error 'object doesn't support this property or method" and code below is highlighted

  .AutoFilter

 Sub Delete_unwanteditems()
     With Sheets("file Names")
    
     .Range("A1").AutoFilter 1, Array("*~", "", "*copy*"), xlFilterValues

   .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilter
   End With
 end Sub

See sample Data below

Macro to Delete Tilde & Text Containing Copy.xlsm
	A	B
1	File Name	Date Modified
2	~$Copy Sales templates from Network.xlsm	22/07/2021
3	~$ECM BR1 sales Report Import Template 2021 Ver 1.1.xlsm	22/07/2021
4	~$B2 sales Report Import Template 2020.xlsm	26/10/2020
5	Copy Sales templates from Network.xlsm	22/07/2021
6	BR2 Sales Report Import Template 2021 Ver 1.1.xlsm	22/07/2021
7	BR3 Sales Report Import Template 2021 Ver 1.1.xlsm	22/07/2021
8	BR4 Sales Report Import Template 2021 Ver 1.1.xlsm	22/07/2021
9
Sheet1

Your assistance in amending my code is most appreciated

 

[Fluff]

Use

.AutoFilterMode=False

 

[howard]

thanks for Pointing this out Fluff

when running the Macro the list of files in Col A containing "~" and "Copy" are not being deleted in Col A

Kindly amend my code

 

[Fluff]

Hadn't noticed that you have 3 criteria, which you cannot do unless looing for exact matches. Try

.Range("A1").AutoFilter 1, "*~", xlOr, "*copy*"

then after deleting the visible row run the filter again for the blanks

 

[howard]

Thanks for amending Code

it now deletes all the rows in Col A

i only want items containing either a Tilde ~ and tect containing Copy to be deleted


Kindly test & amend

 

[Fluff]

Forgot you need to escape the tilde

.Range("A1").AutoFilter 1, "*~~*", xlOr, "*copy*"

 

[howard]

Many Thanks Fluff. code works perfectly

 

[Fluff]

You're welcome & thanks for the feedback.