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?

	Column A	Column B
Row 1	{	XX
Row 2	{AB}	XX
Row 3	{	XX
Row 4	{CD}	XX

<tbody>
</tbody>

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.