Deleting Rows on the basis of cell comment

[Radhakant Panigrahi]

Hi,

i need to delete the rows where the comment in the column A contains some
charaters...for example below are the comment in column A...and i need to
delete the rows where the comment in column A is BE, IE and AT. Is there any
formula by which the rows with BE, IE and AT in column A can be deleted...

DE
DE
IT
IT
PT
PT
IE
BE
IT
BE
AT

rgds
radha

 

[Dave Peterson]

First, there are no formulas that delete rows.

Second, do you really mean comments (like Insert|Comment stuff) or do you mean
values in the cell?

If you really meant comments, then you'll have to use code to inspect those
comments.

But if you meant values, you could:
Insert a new column B
Add headers to row 1 if you don't have them.
Put this in B2:
=or(a2={"be","ie","at"})

Now drag this down as far as you need.

Apply data|filter|autofilter to column B.
Show the Rows that have TRUE in column B.
Delete those visible rows.
Remove the filter (data|filter)
delete column B
Delete the header row if you added it.

 

[Jacob Skaria]

Do you mean a cell comment..Then try the below version

Sub DeleteRows()
Dim lngRow As Long
For lngRow = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Not Range("A" & lngRow).Comment Is Nothing Then
Select Case UCase(Trim(Replace(Range("A" & _
lngRow).Comment.Text, vbLf, "")))
Case "BE", "IE", "AT"
Rows(lngRow).Delete
End Select
End If
Next
End Sub

 

[ck13]

You can do a auto filter, then select those comments that you want to delete.
If the data is huge, you might need to do a macro.

 

[Jacob Skaria]

A formula cannot delete rows..If you are looking for a macro try the below

Sub DeleteRows()
Dim lngRow As Long
For lngRow = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
Select Case UCase(Range("A" & lngRow))
Case "BE", "IE", "AT"
Rows(lngRow).Delete
End Select
Next
End Sub