Delete rows based on conditions

[nadjalil]

Hi
I have a file (attached) where i just need those rows which has 2015 in Column 'C' i want to delete everything else...
can someone help me to get a macro for this...
thanks in advance.

 

[dourpil]

Hi,

I came up with this, which seems to work (although it deletes what seems to be header rows.. I'm not sure what the exact requirements were):

Private Sub deleterows()

Dim n As Integer
n = ThisWorkbook.Worksheets("Sheet1").UsedRange.Rows.Count

Dim x As Integer

For x = 9 To n
  If InStr(1, ThisWorkbook.Worksheets("Sheet1").Cells(x, 3).Value, "2015", vbTextCompare) = 0 Then
  ThisWorkbook.Worksheets("Sheet1").Rows(x).Delete
  x = x - 1
  n = n - 1
  If x >= n Then Exit Sub
  End If
Next x

End Sub

 

[Marc L]

Hi !

So easy with a filter and Macro recorder ‼​

Sub Macro1()
    With Sheet1.Cells(1).CurrentRegion.Columns(3)
        .AutoFilter 1, "<>2015*"
        .EntireRow.Delete
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[nadjalil]

it's not working for me exactly i need to delete all the rows in sheet1 based on column 'C' and need to keep only those cells which contains 2015 in column 'C'...
i hope it'll understand
thanks for your review.

 

[Jagdev Singh]

Hi

The code is working fine. Please let us know what exactly you are doing to make it run.

Regards,
JD

 

[nadjalil]

thanks Marc
could you please let me know if i need to add more filler let say based on few more criteria e.g. Name, School etc
how i can amend the same code.
thanks again for your help

 

[Jagdev Singh]

Hi

The following code you can repeat to make it workable for your other requirements.

With Sheet1.Cells(1).CurrentRegion.Columns(3) - Name of the sheet and cololumn where you want to add the filter.
.AutoFilter 1, "<>2015*" - Is the critria or filter you want to add in a particular column.
.EntireRow.Delete - This code will delete the data.
End With
Regards,
JD

 

[Marc L]

how i can amend the same code.

Easier is to start to use filters on Excel.
Once it's correct, unset filters and activate Macro recorder (like I did)
and reproduce the necessary actions to filter data …

With the generated code, you can read the purpose of a statement
just by placing text cursor in it and hit F1 key.
​