Apply filter and delete rows thru Messege Box

[Prabhu]

Hi Frineds,

My macro is not working, i need to apply filter thur message box and delete after the given date rows.I have attached the macro sheet for your reference .

Kindly help to correct the same.

Regards,

Prabhu

[alansidman]

Try this:

Sub MYFILTER()
Dim W1Startdate As Date
W1Startdate = InputBox("Please Enter the Start Data in DD/MM/YYYY format")
range("A2:T" & range("A1").End(xlDown).Row).AutoFilter Field:=7, Criteria1:=">" & W1Startdate
'range("A4:T" & range("A2").End(xlDown).Row).SpecialCells(12).EntireRow.Delete


End Sub

[Prabhu]

Hi,

I tried, but it is not deleting the record after the given date. i.e it is struck on the filter.

Sub MYFILTER()
Dim W1Startdate As Date
W1Startdate = InputBox("Please Enter the Start Data in DD/MM/YYYY format")
range("A2:T" & range("A1").End(xlDown).Row).AutoFilter Field:=7, Criteria1:=">" & W1Startdate
range("A3:T" & range("A2").End(xlDown).Row).SpecialCells(12).EntireRow.Delete
End Sub

Kindly help

[alansidman]

Try this:

Option Explicit

Sub NewDelete()
    Dim W1Startdate As Date
    W1Startdate = InputBox("Please Enter the Start Data in DD/MM/YYYY format")
    Dim lr As Long
    lr = Range("G" & Rows.Count).End(xlUp).Row
    Dim i As Long
    
    Application.ScreenUpdating = False
    For i = lr To 3 Step -1
    If Range("G" & i) >= W1Startdate Then
    Range("G" & i).EntireRow.Delete
    End If
    Next i
    
    Application.ScreenUpdating = True
    MsgBox "task complete"

End Sub

Please use code tags when posting VBA Code in the forum. It makes for easier reading and is a forum standard.

[Prabhu]

Thanks it is working!

But it is taking huge amount of time when we delete larger data. i think it is deleting row by row.

can we make change to delete the enter in one short now like deleting manually?

[Admin]

Hi

Try

Sub MYFILTER()
    
    Dim W1Startdate As String
    Dim dtFilter    As Date
    
    W1Startdate = Application.InputBox("Please Enter the Start Data in DD/MM/YYYY format", Type:=2)
    If W1Startdate = "False" Then Exit Sub
    
    If IsNumeric(Replace(W1Startdate, "/", vbNullString)) Then
        dtFilter = DateSerial(Split(W1Startdate, "/")(2), Split(W1Startdate, "/")(1), Split(W1Startdate, "/")(0))
        Range("A2:T" & Range("A1").End(xlDown).Row).AutoFilter Field:=7, Criteria1:=">" & dtFilter
        Range("A3:T" & Range("A2").End(xlDown).Row).SpecialCells(12).EntireRow.Delete
        If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    End If
    
End Sub

[Prabhu]

No Sir,

It is not deleting.

[Admin]

Any error ? how many rows of data you have ?

[Prabhu]

I found 2 issues:

1.It is taking MM/DD/YYYY instead of DD/MM/YYYY.

2. If the input dates between 1- 10 example 08/10/2014(10th Aug 2014) it is deleting correctly but the input dates between 11-31 example 08/15/2014 it is not taking any action.

I have attached the data for your reference. Kindly help to correct the same.

Regards,

Prabhu

[Admin]

It's working here. You need to enter the date in dd/mm/yyyy format.