Macro to Remove Specific Rows

[linaeum66]

I'm interested in a macro that removes an entire row if the cell in column "A' contains "II", and the II needs to be case sensitive to capital letters. Also if the macro could ignore the first row which is headings.
Thanks for your help, greatly appreciated.

 

[mumps]

Try:

Sub DelRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("A1:A" & LastRow).AutoFilter Field:=1, Criteria1:="*II*"
    Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

@mumps
Unfortunately autofilter is not case sensitive

 

[mumps]

@Fluff; You're right. I just discovered that.

 

[Fluff]

@linaeum66
If you are happy to have a helper column, then a slightly modified version of @mumps code could be used.
If this is acceptable, what is the first blank column in your data?

 

[Fluff]

Different idea, try

Sub DelRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Columns(1).Replace "II", "[COLOR=#ff0000]|II|[/COLOR]", xlPart, xlByRows, True, False, False
    Range("A1:A" & LastRow).AutoFilter Field:=1, Criteria1:="*[COLOR=#ff0000]|II|[/COLOR]*"
    Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

This assumes that you will not have any data in col A that contains |II|, if you do changes the parts in red to something that wont have.

 

[linaeum66]

Hello Fluff, this is a great idea, however I am unclear about what I should do with the red sections? Could you please clarify? thanks so much.

Different idea, try

Sub DelRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Columns(1).Replace "II", "[COLOR=#ff0000]|II|[/COLOR]", xlPart, xlByRows, True, False, False
    Range("A1:A" & LastRow).AutoFilter Field:=1, Criteria1:="*[COLOR=#ff0000]|II|[/COLOR]*"
    Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

This assumes that you will not have any data in col A that contains |II|, if you do changes the parts in red to something that wont have.

 

[Fluff]

As autofilters as not case sensitive, the macro is replacing any instance of II with |II|. If your data is unlikely to contain |II| in col A, then you can simply run the macro & it should work.
However if your data may contain the value |II| then you will need to change it to something that wont appear.
HTH

 

[mumps]

@Fluff: An interesting approach. I'll have to remember it.

 

[Fluff]

@mumps
Thanks for that, originally I was thinking of using a formula to return "yes" in a helper column if the row needed deleting & then filter on that.
It was only because I was trying to help someone else that I thought about replace.