VBA - Delete rows with certain data

[ChrisFoster]

Hi all,

I have some coding that I have developed over time - but I'm stuck at one particular point.

In 'column B' I have a list of dates, they are all in the same format (UK). What I want is some coding that deletes any row that has a date from the current year (I'll be using this code going into next year so ideally the code will already know what year it currently is rather than just using '2019').

Please can someone help me?

Kind Regards,

Chris

 

[Akuini]

Maybe this:

Sub delYear()
    With Range("B1:B" & Cells(Rows.count, "B").End(xlUp).Row)
        .AutoFilter Field:=1, Criteria1:=xlFilterThisYear, Operator:=xlFilterDynamic
        .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    End With
End Sub

 

[ChrisFoster]

Great thanks - I had to change .AutoFilter Field:=1 to .AutoFilter Field:=2 but that has worked brilliantly. Thanks so much

 

[ChrisFoster]

Hi,

Sorry, you might be able to help me further actually as I've found an issue. Some workbooks I use this code on wont necessarily have dates from this year. So, is there a way of using this code you've supplied but skipping to the next part of the code if there is no date from this current year?

Cheers

Chris

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...elete-rows-with-certain-data.html#post5188393

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[ChrisFoster]

Cross posted https://www.excelforum.com/excel-pr...elete-rows-with-certain-data.html#post5188393

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Hi,

Apologies, I was not aware there was any affiliation between the two forums. That's totally my fault. I'll avoid this in future.
I'll post the same on the other forum.

Regards,

Chris

 

[Fluff]

There is no "affiliation" between the two sites, but all forums have a similar rule.
Please read this to understand about cross posting https://www.excelguru.ca/content.php?184

 

[Akuini]

Ok, try this:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] delYear1()
[COLOR=Royalblue]Dim[/COLOR] c [COLOR=Royalblue]As[/COLOR] Range
    [COLOR=Royalblue]With[/COLOR] Range([COLOR=brown]"B1:B"[/COLOR] & Cells(Rows.count, [COLOR=brown]"B"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)
    [COLOR=Royalblue]Set[/COLOR] c = .Find(What:=Year([COLOR=Royalblue]Date[/COLOR]), LookIn:=xlFormulas, lookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=[COLOR=Royalblue]False[/COLOR], SearchFormat:=[COLOR=Royalblue]False[/COLOR])
    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] c [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        .AutoFilter Field:=[COLOR=crimson]1[/COLOR], Criteria1:=xlFilterThisYear, [COLOR=Royalblue]Operator[/COLOR]:=xlFilterDynamic
        .Offset([COLOR=crimson]1[/COLOR]).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[ChrisFoster]

There is no "affiliation" between the two sites, but all forums have a similar rule.
Please read this to understand about cross posting https://www.excelguru.ca/content.php?184

Read and understood.

Ok, try this:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] delYear1()
[COLOR=Royalblue]Dim[/COLOR] c [COLOR=Royalblue]As[/COLOR] Range
    [COLOR=Royalblue]With[/COLOR] Range([COLOR=brown]"B1:B"[/COLOR] & Cells(Rows.count, [COLOR=brown]"B"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)
    [COLOR=Royalblue]Set[/COLOR] c = .Find(What:=Year([COLOR=Royalblue]Date[/COLOR]), LookIn:=xlFormulas, lookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=[COLOR=Royalblue]False[/COLOR], SearchFormat:=[COLOR=Royalblue]False[/COLOR])
    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] c [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        .AutoFilter Field:=[COLOR=crimson]1[/COLOR], Criteria1:=xlFilterThisYear, [COLOR=Royalblue]Operator[/COLOR]:=xlFilterDynamic
        .Offset([COLOR=crimson]1[/COLOR]).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

Thanks for this - it debugs however giving the Run Time 1004 error at the below stage;

.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete

Any ideas?

 

[Akuini]

Did you use the exact same code or you change something, maybe the range?
I'm curious because you said earlier you need to change .AutoFilter Field:=1 to .AutoFilter Field:=2