Macro to Remove Specific Rows

linaeum66

New Member
Joined
Jul 2, 2017
Messages
25
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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Try:
Code:
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
 
Upvote 0
@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?
 
Upvote 0
Different idea, try
Code:
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.
 
Upvote 0
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
Code:
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.
 
Upvote 0
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
 
Upvote 0
@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.
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,313
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top