Delete cells specific to names

[vbanoob123]

Hello Everybody,

I have an excel file where I would like to delete rows if a list names appear in either column L or M.

Would anybody know what VBA is required for this please?

Thank you for your help.

Noob

 

[RudiS]

VBA is not really required as you can simply activate the autofilter feature, apply the criteria to extract the records to delete and then select all rows and delete. Do this for criteria in column L and M and the resulting list will be cleaned from the unwanted records.

 

[hiker95]

vbanoob123,

Welcome to the MrExcel forum.

I would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

See reply #2 at the next link, if you want to show small screenshots.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729

 

[vbanoob123]

Hi rudi,

This file is going to be looked at everyday and edited, there are about six names that need to be excluded across two columns and then delete the rows. Isn't there a faster way than doing this manually?

I did try this code from another discussion on here

Private Sub CommandButton1_Click()


With ActiveSheet
.AutoFilterMode = False
With Range("l11", Range("L" & Rows.Count).End(xlUp))
.AutoFilter 1, "*John_Smith*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With


End Sub

---

But this only looks across one column and deletes only rows with John Smith, what happens if i wanted to exclude more like Daniel Adams & Joe Bloggs?

Many Thanks,

Noob

 

[RudiS]

Try this...
Modify the code and criteria as required...

Sub FilterAndDeleteData()
    'Filter for column L (list names in Criteria line)
    ActiveSheet.Range("A1").CurrentRegion.AutoFilter _
        Field:=12, _
        Criteria1:=Array("Janet Leverling", "Margaret Peacock"), _
        Operator:=xlFilterValues
    With ActiveSheet.AutoFilter.Range
        On Error Resume Next
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        On Error GoTo 0
    End With
    ActiveSheet.AutoFilterMode = False
    'Filter for column M (list names in Criteria line)
    ActiveSheet.Range("A1").CurrentRegion.AutoFilter _
        Field:=13, _
        Criteria1:=Array("Road", "Sea"), _
        Operator:=xlFilterValues
    With ActiveSheet.AutoFilter.Range
        On Error Resume Next
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        On Error GoTo 0
    End With
    ActiveSheet.AutoFilterMode = False
End Sub

 

[vbanoob123]

It works!!! Thank you very much RudiS!!