Auto Filter

[vmjan02]

I have this code and not sure its not selecting the data,

screen shot is attached, not sure as what is the actula problem.

Sub AutoData()

    
  Dim lRow2 As Long
  Dim lColumn2 As Long

  lRow2 = Range("A1").End(xlDown).Row
  lColumn2 = Range("A1").End(xlToRight).Column
        
  Cells(lRow2, lColumn2).Select
    
    Selection.AutoFilter
    Range("A1").Select
    ActiveSheet.Range("A1" & lRow2).AutoFilter Field:=1, Criteria1:=Range("AU1").Value
       
    Range("A1:AS" & lRow2).SpecialCells(xlCellTypeVisible).Delete
    Selection.AutoFilter
    
End Sub
[ATTACH type="full"]97901[/ATTACH][ATTACH type="full"]97902[/ATTACH]

 

[DanteAmor]

Try this:

Sub AutoData()
  Dim lr As Long
  Dim lc As Long

  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  lr = Range("A" & Rows.Count).End(xlUp).Row
  lc = Range("A1").End(xlToRight).Column
        
  ActiveSheet.Range("A1", Cells(lr, lc)).AutoFilter Field:=1, Operator:= _
        xlFilterValues, Criteria2:=Array(0, Format(Range("AU1").Value, "mm/dd/yyyy"))
       
  Range("A2:AS" & lr).SpecialCells(xlCellTypeVisible).Delete
  Selection.AutoFilter
    
End Sub

 

[MARK858]

@DanteAmor

With the data set below

Book1.xlsb
	A	B	C	D	E	F	G	H	I																																					AT	AU
1	Order Date	Site	SP Name	Category Name	Ship Via	Quantity	Order Total																																								24-Aug-2023
2	18-Feb-2014	Off Site	Janet Leverling	Seafood	Road	91	2094.351
3	10-Mar-2014	On Site	Janet Leverling	Dairy Products	Sea	49	1460.7846
4	05-Sep-2013	Off Site	Margaret Peacock	Condiments	Road	70	1400.661
5	27-Nov-2013	On Site	Laura Callahan	Confections	Sea	6	151.8594
6	14-Oct-2013	Off Site	Michael Suyama	Confections	Sea	24	354.2892
7	06-Jun-2013	Off Site	Laura Callahan	Seafood	Sea	20	632.073
8	12-Feb-2014	On Site	Margaret Peacock	Condiments	Sea	12	377.2716
9	21-May-2013	On Site	Laura Callahan	Grains/Cereals	Sea	70	665.3838
10	07-Aug-2013	On Site	Laura Callahan	Produce	Road	21	667.3902
11	11-Feb-2013	On Site	Janet Leverling	Dairy Products	Rail	30	629.2116
12	25-Apr-2013	On Site	Margaret Peacock	Condiments	Sea	9	448.248
13	25-Nov-2012	Off Site	Janet Leverling	Meat/Poultry	Sea	24	270.9096
14	01-May-2013	On Site	Nancy Davolio	Beverages	Road	60	3425.4606
15	23-Aug-2023	On Site	Anne Dodsworth	Condiments	Air	30	1456.6692
16	04-Feb-2013	On Site	Laura Callahan	Grains/Cereals	Air	12	240.3234
17	23-Jul-2012	On Site	Anne Dodsworth	Beverages	Sea	28	281.4204
18	13-Jan-2014	On Site	Margaret Peacock	Beverages	Road	28	667.8462
19	11-Mar-2014	On Site	Robert King	Dairy Products	Road	50	2440.5234
20	16-Dec-2012	On Site	Janet Leverling	Dairy Products	Sea	15	657.3696
21	22-Apr-2014	On Site	Janet Leverling	Beverages	Sea	30	574.8108
Sheet1

I get the result below with the code if I comment out the Delete and Selection.Autofilter lines (note the date in AU1)

Book1.xlsb
	A	B	C	D	E	F	G	H	I																																					AT	AU
1	Order Date	Site	SP Name	Category Name	Ship Via	Quantity	Order Total																																								24-Aug-2023
15	23-Aug-2023	On Site	Anne Dodsworth	Condiments	Air	30	1456.6692
Sheet1

@vmjan02 try

Sub AutoData()

   
    Dim lRow2 As Long
    Dim lColumn2 As Long
   
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False

    lRow2 = Range("A" & Rows.Count).End(xlUp).Row
    lColumn2 = Range("A1").End(xlToRight).Column
        
    ' Range(Cells(1, "A"), Cells(lRow2, lColumn2)).Select 'this line isn't really needed, just to demo the syntax
   
   
    With ActiveSheet.Range("A1:A" & lRow2)
        .AutoFilter Field:=1, Criteria1:=Format(CLng(Range("AU1").Value), "dd-mmm-yyyy")
      
        On Error Resume Next
        .Offset(1).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(12).EntireRow.Delete
        On Error GoTo 0
        .AutoFilter
    End With

End Sub

 

[vmjan02]

Thanks guys this perfect and working as perfect.

 

[MARK858]

You're welcome, happy we could help