Deleting specific rows with VBA

[Mr2017]

Hi Guys

I have data in two columns and would like to delete data in the second column which has the letter 'N' in it, using the auto filter.

The amount of rows will vary.

In the example below, there are only 5 rows and I would like to delete rows 3 and 4, as they're the rows with the letter 'N'.

Does anyone know how to do this?

TIA

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Number[/TD]
[TD="class: xl65, width: 64"]Letter[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]Y[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]N[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]N[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]Y[/TD]
[/TR]
</tbody>[/TABLE]

 

[Domenic]

The following macro assumes that the sheet containing the data is the active sheet...

Sub Delete_N_Rows()

    With ActiveSheet.UsedRange
        .AutoFilter field:=2, Criteria1:="N"
        .Offset(1, 0).EntireRow.Delete
        .AutoFilter
    End With
    
End Sub

Hope this helps!

 

[James006]

Hi,

You are right ...Using AutoFilter is indeed the best solution ...

Once you have filtered your database, you can set a new range with SpecialCells(xlCellTypeVisible) ...

which allows you to delete these rows ...

Hope this will help

 

[Mr2017]

Awesome!

Thank you very much Domenic

!

I was in the middle of testing a complicated version suggested on youtube, where they'd declared lots of variables, and it wasn't working, so this solution is much appreciated! It's much more efficient!!

Thanks again!

 

[Domenic]

You're very welcome, cheers!

 

[hiker95]

Mr2017,

Here is another macro solution for you to consider.

Sub DeleteColumnB_N()
' https://www.mrexcel.com/forum/excel-questions/1113838-deleting-specific-rows-vba.html
Dim Addr As String
Addr = Range("B1", Cells(Rows.Count, "B").End(xlUp)).Address
Range(Addr) = Evaluate(Replace("IF(ISNUMBER(SEARCH(""N"",@)),""#N/A"",@)", "@", Addr))
Columns("B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End Sub

 

[Mr2017]

Ok, thanks.

 

[Akuini]

Sub Delete_N_Rows()

    With ActiveSheet.UsedRange
        .AutoFilter field:=2, Criteria1:="N"
        .Offset(1, 0).EntireRow.Delete
        .AutoFilter
    End With
    
End Sub

Hi, Domenic. Thanks for the code.
I just realize that we can delete only visible cell in a filter range without needing to use specialCells(xlCellTypeVisible) and also using UsedRange is a nice trick.