excel vba: how to remove the entire row that contains "{"

[huiyin9218]

Hi,

May I know how to write a code to delete the entire row when there is "{" only in column A?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD]{[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD]{AB}[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD]{[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]{CD}[/TD]
[TD]XX[/TD]
[/TR]
</tbody>[/TABLE]

From the sample table above, i would like to delete row 1 and 3.


I would be so grateful for your help.

 

[My Aswer Is This]

Try this:

Sub Filter_Me_Please()
'Modified  8/16/2018  4:22:42 AM  EDT
Application.ScreenUpdating = False
Dim Lastrow As Long
Dim c As Long
Dim s As Variant
c = "1" ' Column Number Modify this to your need
s = "{" 'Saerch Value Modify to your need
Lastrow = Cells(Rows.Count, c).End(xlUp).Row
With ActiveSheet.Cells(1, c).Resize(Lastrow)
    .AutoFilter 1, s
    counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

 

[huiyin9218]

Try this:

Sub Filter_Me_Please()
'Modified  8/16/2018  4:22:42 AM  EDT
Application.ScreenUpdating = False
Dim Lastrow As Long
Dim c As Long
Dim s As Variant
c = "1" ' Column Number Modify this to your need
s = "{" 'Saerch Value Modify to your need
Lastrow = Cells(Rows.Count, c).End(xlUp).Row
With ActiveSheet.Cells(1, c).Resize(Lastrow)
    .AutoFilter 1, s
    counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

Oh! so its like filter "{" and delete all "{", correct?
Thank you so much!

 

[My Aswer Is This]

Oh! so its like filter "{" and delete all "{", correct?
Thank you so much!

Yes. That should do what you wanted correct?

 

[huiyin9218]

Yes. That should do what you wanted correct?

Yes yes. Thank you

 

[My Aswer Is This]

Yes yes. Thank you

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.