• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code to filter I column with other than current month and delete it

Please help me with a VBA code to delete (the data is huge) all Rows which do not equal to the current month in the I column. The date format of I column is MM/DD/YYYY.

I could get the code to filter for current month but I am looking for other than current month.
Code:
Sub DateFilter()
Sheets("BB-invoices").Range("A:AB").AutoFilter Field:=9, Criteria1:=xlFilterThisMonth, Operator:=xlFilterDynamic
End Sub
 

AlanSidman

Well-Known Member
Code:
Option Explicit

Sub delX()
    Dim m As Long
    Dim y As Long
    Dim i As Long
    Dim lr As Long
    lr = Range("I" & Rows.Count).End(xlUp).Row
    m = Month(Date)
    y = Year(Date)
    For i = lr To 1 Step -1
        If Year(Range("I" & i)) <> y Then
            Range("I" & i).EntireRow.Delete
        ElseIf Month(Range("I" & i)) <> m Then
            Range("I" & i).EntireRow.Delete
        End If
    Next i

End Sub
 
Thank you...But it seems it didn't work. I ran the code in the sample file which has 1600 lines and waited for 10 mins...still code is running without success. Is there a way to delete together. My file sometimes will have more 1 mil line items.
 

Attachments

Marc L

Excel Ninja

Could be less than a minute just operating manually !

Via a VBA code, do not forget to desactivate the display,
see ScreenUpdating property within VBA inner help …
 
Thank you! It worked!

Can I ask one more option?

Can we bring a dialog box to select the Month to retain the data? it would help to select the month?
 

AlanSidman

Well-Known Member
Code:
Option Explicit


Sub delX()
    Dim m As Long
    Dim y As Long
    Dim i As Long
    Dim lr As Long
    Application.ScreenUpdating = False
    lr = Range("I" & Rows.Count).End(xlUp).Row
    m = InputBox("What month to keep, enter the month number.")
    y = InputBox("What year is the month in?  Enter as yyyy")
    For i = lr To 1 Step -1
        If Year(Range("I" & i)) <> y Then
            Range("I" & i).EntireRow.Delete
        ElseIf Month(Range("I" & i)) <> m Then
            Range("I" & i).EntireRow.Delete
        End If
    Next i
Application.ScreenUpdating = True
End Sub
 
Thank you...the month and year boxes work! The code seems to be having error. After the deletion process, it always shows error "Run time error 13; Type mismatch"
 

AlanSidman

Well-Known Member
I'm guessing that your worksheet dates are text values. Cannot be sure of the error without seeing your worksheet. Suggest you upload a small sample (maybe 25 records) of your worksheet for analysis.
 

AlanSidman

Well-Known Member
Using your test.xlsx file, I modified the code slightly and this worked for me.

Code:
Option Explicit

Sub delX()
    Dim m As Long
    Dim y As Long
    Dim i As Long
    Dim lr As Long
    Dim ws As Worksheet
    Set ws = Sheets("BB-invoices")
    Application.ScreenUpdating = False
    lr = ws.Range("I" & Rows.Count).End(xlUp).Row
    m = InputBox("What month to keep, enter the month number.")
    y = InputBox("What year is the month in?  Enter as yyyy")
    For i = lr To 1 Step -1
        If Year(ws.Range("I" & i)) <> y Then
            ws.Range("I" & i).EntireRow.Delete
        ElseIf Month(ws.Range("I" & i)) <> m Then
            ws.Range("I" & i).EntireRow.Delete
        End If
    Next i
Application.ScreenUpdating = True
End Sub
 

AlanSidman

Well-Known Member
What kind of error are you getting? Your sample worksheet has only one month of data showing. November? When you get an error, click on debug and tell us what line of code is highlighted. This code worked flawlessly for me in your earlier sample once the link was removed as I did not have access to your other workbooks. Telling it does not work is not satisfactory. Please advise your issues as requested above.

Also change this line of code

For i = lr To 1 Step -1

to

For i = lr To 2 Step -1
 
Last edited:
The error is the same which I communicated before. The error code - Run time error : 13.

Please see the attached file which I ran after I changed the line based on your latest response. It took almost 10 mins to run the code and finally resulted with Run time error.
 

Attachments

Marc L

Excel Ninja

As yet wroted in post #4, so less than a minute,
without any loop, maximum ten codelines …
In fact less than half a second with your last attachment !
 

AlanSidman

Well-Known Member
can not replicate your error. Ran code with sample workbook and it took less than 1 second to delete records. When you run debug, you still have not told us which line of code is highlighted and what the error message is. Is the sample workbook represent your actual workbook. Also, when you attach the sample workbook, the code I have provided is not in the workbook. I have to add it. Have you made any changes to my code. I cannot see what you have run.
 
I got the error on the below line:

If Year(ws.Range("I" & i)) <> y Then

Run-Time error '13':

Type mismatch

I have added to code to the test file. Please check and let me know.

I entered the month as 12 and year as 2017.
 

Attachments

AlanSidman

Well-Known Member
In your latest test you need to change this line of code

For i = lr To 1 Step -1

to

For i = lr To 3 Step -1

as your data starts in row 3. The latest test sheet differs in layout from earlier sheets. You need to begin to read the VBA and understand it and not just take it for granted. I hope the latest sheet is truly representative of your actual worksheet.
 
Hello,

As you know I am new to these kind of forums and I am still learning. But the way you respond does not suite to professionals. I am sorry to say this but this is not 1st your response carry a different tone which cannot be a accepted by new members. I always take directions and but such kind of responses are not directions.
 

AlanSidman

Well-Known Member
It is frustrating for those of us who help to not receive an accurate presentation of what the issue is. You have consistently presented a different scenario with each test file. What do you expect when you are inconsistent. We are not mind readers and need an accurate understanding of the issues. If you are unhappy with the responses you receive here, then I suggest you look elsewhere for assistance.
 

Marc L

Excel Ninja
Alan, I agree …

Niranjanrajrishi, direction : warming a couple of neurons,
manually on worksheet, just apply an easy filter, at child level !

If really a code is needed (as yet that needs less than 10 seconds manually !),
once the filter result is accurate, cancel the filter,
activate the Macro recorder and redo the filter then delete the rows :
you will get your own free code base as any beginner can (must) do !

In case of an optimization need, post your generated code here
(between code tags via the Code icon)
with a crystal clear & complete explanation of what you expect …
 

Hui

Excel Ninja
Staff member
Niranjanrajrishi,

I will be sorry to see you go, but that is your choice.

Please remember that people answering questions here at Chandoo.org,
1. Are unpaid volunteers, even though my Title says Staff, I am a volunteer
2. Do not know your problem or data
3. Do not necessarily have the same computer or Excel setup that you have
4. May not speak the same primary language as you and I
5. Cannot read minds

As such we try and resolve issues as best we can.

We ask for information to clarify assumptions, not to belittle you. You wouldn't believe how many times we are told my data is a Date (a number), to find out it is actually Text;

and as you do we get frustrated when what you supply us as data doesn't match your descriptions

We are all humans

I hope you have a better experience elsewhere, but please remember that you will always be welcome back here

When you come back, Please read: https://chandoo.org/forum/threads/new-users-please-read.294/
 

sperly

New Member
Hey- I know ths is Old, but this is the closest to what i am looking for.
How do i filter by any date with the Current month and greater so future months as well?
So far i have a XlfilterThisMonth but i cant figure out how to have the greater months as well.

>>> use code - tags <<<
Code:
.AutoFilter Field:=10, Criteria1:=xlFilterThisMonth, Operator:=xlFilterDynamic
Thanks!
 
Last edited by a moderator:
Top