find a word [include wild card] and delete Row

Please help. I just want to find "Manually Constructed" but best to look for Manually* [* being the wild card* and then delete the row using VBA

appreciate your help
JagwarmanAsked:
Who is Participating?
 
ProfessorJimJamCommented:
here is the VBA solution for you.  run this in a backed up file.

assuming your data is in A column. change  A1 in line 7 to the column Letter , if your data is not in column A

it will delete the entire row for the cell that has text Manually in it. regardless whether there is any prefix or suffix


Sub RemoveRows()

Dim i As Long

i = 1

Do While i <= ThisWorkbook.ActiveSheet.Range("A1").CurrentRegion.Rows.Count

    If InStr(1, ThisWorkbook.ActiveSheet.Cells(i, 1).Text, "Manually", vbTextCompare) > 0 Then
        ThisWorkbook.ActiveSheet.Cells(i, 1).EntireRow.Delete
    Else
        i = i + 1
    End If

Loop

End Sub

Open in new window

0
 
Rob HensonFinance AnalystCommented:
Will it be in a particular column?

Use Auto Filter and set Filter to 'Contains' ~ Manually

Thanks
Rob H
0
 
JagwarmanAuthor Commented:
Column A need VBA
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Rob HensonFinance AnalystCommented:
Do it manually and record the steps with the VBA Recorder. That will give you the steps required.

Thanks
Rob H
0
 
Rob HensonFinance AnalystCommented:
BTW, when data is filtered, selecting the visible rows as a block and deleting will only delete the visible rows. When the filter is then removed the rows which were hidden will re-appear and will not be affected by the delete.

Just saying because for a simple filter such as this I see it as a bit overkill to put in VBA.

Thanks
Rob H
0
 
JagwarmanAuthor Commented:
Exactly what I needed thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.