Macro to delete items in Col N starting with Z1 and X1

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,561
Office Version
  1. 2021
Platform
  1. Windows
I have a workbook that I open and copy the data

I first want to open and delete all rows on sheet "Imported data" in Col N starting with Z1 & X1 eg Z100418 , X100419

If Z1 or X1 does not exist, then Exit Sub

I have tried to write code to do this , but get a run time error "Object does'nt support this property or method" and code below is highlighted


Code:
  .AutoFilter


See full code below



Code:
 Sub Open_File()

ChDir "C:\extract"
Dim A As Variant
Dim LR As Long
With Sheets("Imported Data")
.UsedRange.ClearContents
End With

If TypeName(A) = "Boolean" Then Exit Sub

Dim FileAry As Variant, Fle As Variant
   With Application.FileDialog(3)
      .InitialFileName = "C:\extract\"
      .AllowMultiSelect = True
      .InitialFileName = "Sales *BR*.xls*"
      If .Show Then Set FileAry = .SelectedItems()
   End With
   For Each File In FileAry
   If TypeName(A) = "Boolean" Then Exit Sub
   
      With Workbooks.Open(File)
   With .Sheets(1)
    
     .Range("A1").AutoFilter 3, Array("Z1*", "", "X1*"), xlFilterValues

   .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilter
     

      .Range("a1", .Range("AH" & Rows.Count).End(xlUp)).Copy _
      Destination:=ThisWorkbook.Sheets("Vat Analysis").Range("A" & Rows.Count).End(xlUp)
             '  .Range("a1:M" & Rows.Count).End(xlUp).UnMerge
        

End With

.Close savechanges:=False
End With
Next
Application.ScreenUpdating = True
End Sub


It would be appreciated if someone could kindly assist me
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
I replaced .Autofilter with
Code:
 .AutoFilterMode = False

macro now runs perfectly
 
Upvote 0
Although the macro runs, the Items Starting with Z1 an X1 in Col N is not being deleted for e.g. Z104985, Z101983 etc


Kindly test & amend my code
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top