VBA to delete row based on cell value

[dumbitdown]

I'm trying to get some code to help me delete rows from a data set when a value in column I = 2016, everything I've seen so far just ends up deleting all rows of data, now those that I've tried to specify as having 2016 in column I

There are several hundred rows of data and it gets refreshed with new stuff on a fairly regular basis so there is no defined volume of records in the list that is being used.

Would someone be able to provide me with some code to help with this?

Thanks

 

[My Aswer Is This]

Is that all that is in column "I" just "2016"

So look through all rows and any row that has "2016" in Column "I" then delete that row.

Is that what you want?

And your willing to use Vba?

This cannot just be done with a just a formula.

 

[Joe4]

I would probably use Filters to identify all the rows with that value, and delete the data.
There have been lots of posts on this site regarding that. Here are a few, but there are others:
http://www.mrexcel.com/forum/excel-questions/553208-visual-basic-applications-filter-delete.html
http://www.mrexcel.com/forum/excel-...-basic-applications-delete-filtered-rows.html

 

[dumbitdown]

Yeah sorry, I should have included that, I am trying to use VBA to delete the row.

Column I contains multiple values but I only want to delete those rows that have 2016 in it.

 

[My Aswer Is This]

Try this:

Sub FilterMini()
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "I").End(xlUp).Row
With ActiveSheet.Range("I1:I" & Lastrow)
.AutoFilter Field:=1, Criteria1:="2016", Operator:=xlFilterValues
.SpecialCells(xlCellTypeVisible).Rows.Delete
End With
ActiveSheet.AutoFilterMode = False
End Sub

 

[My Aswer Is This]

My script assumes the cell value is only "2016"
Not: 1/15/2016

 

[Joe4]

Yeah sorry, I should have included that, I am trying to use <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> to delete the row.

You did. It is right in your title!

 

[dumbitdown]

The script is correct to assume that, it's not date related it's an outcome code that means those records can be removed.

The filter itself works, however I'm getting an error message when it's trying to delete, when I select debug it's highlighting

.SpecialCells(xlCellTypeVisible).Rows.Delete

Any thoughts?

 

[dumbitdown]

You did. It is right in your title!

Haha thanks Joe4! It's that kind of day I didn't even realise!!

 

[Joe4]

You may want to take a look at the links I provided, as I think the code provided here does not account for the header row (if there is one), and it will delete that too.