I can use the replace function to delete things I don’t want but there is no function to delete the entire row so instead to do it manually, this code will delete all rows in which a cell contains a word or phrase that I don’t want. Imagine that I have cells that contain “month out of date for” and what I want, it is to delete all cells with “out of date”, so each time it finds it, it will delete the entire row.
When I use the macro ?
To delete an entire row when it finds a specific word or phrase that I don’t want.
How to create the macro ?
Read How to create, edit, hide and select a macro in an excel report
How to create the button to associate it with the macro ?
Read How to create a button and associated it to a macro in an excel report
How is the macro ?
Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.
Sub test()
Dim cde As Range
Dim SrchRng
' change the range
Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A20").End(xlUp))
Do
' change xxxx by the word or phrase to search
Set cde = SrchRng.Find("*xxxx*", LookIn:=xlValues)
If Not cde Is Nothing Then cde.EntireRow.delete
Loop While Not cde Is Nothing
End Sub
This one can be used for multiple columns:
Sub test()
Dim cde As Range
Dim SrchRng
' change the range
Set SrchRng = ActiveSheet.Range("A:B", ActiveSheet.Range("A:B").End(xlUp))
Do
' change xxxx by the word or phrase to search
Set cde = SrchRng.Find("xxxx", LookIn:=xlValues)
If Not cde Is Nothing Then cde.EntireRow.Delete
Loop While Not cde Is Nothing
End Sub
When you are managing a team, “how to be a good manager” is the “must”...
As manager, I am doing many reports, even when I was an ITIL consultant, I still needed to do many reports...
ITIL V3 is going to be obsolete...
Managing an IT service when I start a new company is not an easy task, particularly true, if the service...