Search worksheet for multiple strings of text, then delete those rows

[HTSCF Fareha]

Trying to get a few lines of code to search through the active worksheet, looking for cells that start with specific lines of text in any cell, then if any are found, delete the row(s) that contain them. The sheet may / may not contain these text strings and the text may appear in any row / column combination.

I have cobbled together this, which throws up a compile error: object required, where I have highlighted.

Due to my limited VBA knowledge, I'm not sure if this is the most effective way of performing this function. I'm currently looking for instances that start with "Z INFORMATION" and "More...", but it would be ideal if the search wasn't case sensitive.



Dim SrchRng As Long
Dim i As Long
Dim rng As Range

Set rng = ActiveSheet.UsedRange

Application.ScreenUpdating = False

Set SrchRng = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Application.Calculation = xlCalculationManual
For i = SrchRng To 1 Step -1
If ActiveSheet.Cells(i, 1).Value Like "Z INFORMATION*" Or _
ActiveSheet.Cells(i, 1).Value Like "More...*" Then Cells(i, 1).EntireRow.Delete
Next i
Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

[Paul_Hossler]

Set SrchRng = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row


The keyword Set is used to assign an object to an object variable

The keyword Row is a numeric value

I'm guessing that you really wanted just this



SrchRng = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

[jolivanes]

Does this do what you want? (Not tested)

Sub Maybe_Multiple_Values()
Dim delArr, c As Range, j As Long
delArr = Array("Z INFORMATION", "More...") '<---- Put all the values to be deleted between double quotation marks in the array
Application.ScreenUpdating = False
For Each c In ActiveSheet.UsedRange
For j = LBound(delArr) To UBound(delArr)
If Left(c, Len(delArr(j))) = delArr(j) Then c.EntireRow.Delete: Exit For
Next j
Next c
Application.ScreenUpdating = True
End Sub

[HTSCF Fareha]

Set SrchRng = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row


The keyword Set is used to assign an object to an object variable

The keyword Row is a numeric value

I'm guessing that you really wanted just this



SrchRng = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row


Thanks, Paul for the explanation and yes, the suggested line works fine.

[HTSCF Fareha]

Does this do what you want? (Not tested)

Sub Maybe_Multiple_Values()
Dim delArr, c As Range, j As Long
delArr = Array("Z INFORMATION", "More...") '<---- Put all the values to be deleted between double quotation marks in the array
Application.ScreenUpdating = False
For Each c In ActiveSheet.UsedRange
For j = LBound(delArr) To UBound(delArr)
If Left(c, Len(delArr(j))) = delArr(j) Then c.EntireRow.Delete: Exit For
Next j
Next c
Application.ScreenUpdating = True
End Sub

Thanks, jolivanes for looking at this one. Unfortunately this doesn't work (acknowledged that you mentioned "untested"). No errors, just doesn't delete anything. I do like the idea though of putting things into an array.

[Dave]

jolivanes and HTSF I think it should be ...

If LCase(Left(c, Len(delArr(j)))) = LCase(delArr(j)) Then c.EntireRow.Delete: Exit For
HTH. Dave

[snb]

the string to look for: "bb"

Sub M_snb()
Sheet1.Cells.Replace "*bb*", "=1/0"
Sheet1.Cells.SpecialCells(-4123, 16).EntireRow.Delete
End Sub

[HTSCF Fareha]

jolivanes and HTSF I think it should be ...

If LCase(Left(c, Len(delArr(j)))) = LCase(delArr(j)) Then c.EntireRow.Delete: Exit For
HTH. Dave


Hi Dave, substituted as suggested but alas, still no luck.



Sub Maybe_Multiple_Values() Dim delArr, c As Range, j As Long
delArr = Array("Z INFORMATION", "More...") '<---- Put all the values to be deleted between double quotation marks in the array
Application.ScreenUpdating = False
For Each c In ActiveSheet.UsedRange
For j = LBound(delArr) To UBound(delArr)
If LCase(Left(c, Len(delArr(j)))) = LCase(delArr(j)) Then c.EntireRow.Delete: Exit For
Next j
Next c
Application.ScreenUpdating = True
End Sub

[Dave]

Trialled the code and it works. U must have data in "A1" for the code to work. Dave

[HTSCF Fareha]

Sorry, still no joy my end.

I've ensured that there is data in cell "A1". Just in case I am missing something, does this check all cells in the active worksheet or just those in column "A" and then delete rows?

[Dave]

Google UsedRange. Here's an example VBA-Excel: UsedRange – Excel-Macro (tutorialhorizon.com) (https://excel-macro.tutorialhorizon.com/vba-excel-usedrange/)
It includes all cells in the worksheet including all cells that have eve been used or formatted.

delArr = Array("Z INFORMATION", "More...") '<---- Put all the values to be deleted between double quotation marks in the array
Note jolivanes comments above. Dave

[HTSCF Fareha]

Yep, this is exactly what I have. Ensuring that I have something in cell "A1" too before running.


Dim delArr, c As Range, j As Long
delArr = Array("Z INFORMATION", "More...") '<---- Put all the values to be deleted between double quotation marks in the array
Application.ScreenUpdating = False
For Each c In ActiveSheet.UsedRange
For j = LBound(delArr) To UBound(delArr)
If LCase(Left(c, Len(delArr(j)))) = LCase(delArr(j)) Then c.EntireRow.Delete: Exit For
Next j
Next c
Application.ScreenUpdating = True

[snb]

Why do you ignore the best solution ?
http://www.vbaexpress.com/forum/showthread.php?68162-Search-active-worksheet-for-multiple-strings-of-text-then-delete-those-rows&p=406032&viewfull=1#post406032

[HTSCF Fareha]

the string to look for: "bb"

Sub M_snb()
Sheet1.Cells.Replace "*bb*", "=1/0"
Sheet1.Cells.SpecialCells(-4123, 16).EntireRow.Delete
End Sub

Sorry snb, completely missed this reply!

This works brilliantly!! :thumb

Two questions..

1) How does one add other words / phrases to search for and delete rows?
2) How on earth does this work? It must have something to do with those numbers, but how, I do not know.

[jolivanes]

@Dave
Re Post #6
Thanks Dave. I guess I should be more on the ball as they say in your part of the world.
Have a good one.

[jolivanes]

@HTSCF Fareha
Please give us all a Christmas present. Don't quote whole Posts. Just clutter nobody needs.

[snb]

Sub M_snb()
for each it in split("aa bb cc dd ee")
Sheet1.Cells.Replace "*" & it & "*", "=1/0"
next
Sheet1.Cells.SpecialCells(-4123, 16).EntireRow.Delete
End Sub

Use the F1 key in the VBEditor, search for 'specialcells'

[HTSCF Fareha]

Thanks for this, snb.

Showing my naivity, but do I need to assign 'it' as a variable, as when I run this it fails on this word with a "variable not defined"?

Secondly, how would I trap / handle an error if none of the phrases were found in the worksheet? Ideally I would need it to ignore if the words / phrases were not found and just continue to the next part of my sub.

[snb]

Remove 'option excplicit'
No further actions required.

[HTSCF Fareha]

I've never come accross this before as I always thought that it was best practice to declare your variables at the start of a procedure?


Is this the best way to continue with code execution if there are no instances of the text found?


For Each it In Split("*Z INFORMATION* *More...*")
Sheet1.Cells.Replace "*" & it & "*", "=1/0"
Next
On Error Resume Next
Sheet1.Cells.SpecialCells(-4123, 16).EntireRow.Delete

Thanks!

[Paul_Hossler]

I've never come across this before as I always thought that it was best practice to declare your variables at the start of a procedure?


It's a matter of opinion and personal style/choice/preference

I always use it since I was brought up on strongly typed variables




Is this the best way to continue with code execution if there are no instances of the text found?



For Each it In Split("*Z INFORMATION* *More...*")
Sheet1.Cells.Replace "*" & it & "*", "=1/0"
Next

On Error Resume Next
Sheet1.Cells.SpecialCells(-4123, 16).EntireRow.Delete
On Error Goto 0 ' <<<<<<<<<<<<<<<<<<<<


Yes, but I always go back to normal error catching as soon as possible

[HTSCF Fareha]

Ah, right okay. Thanks for the explanation.

Annoyingly this seems to result in undesired results in that the 'Replace' is leaving a#DIV/0! in the cell where it finds the text and not deleting the row.

[Paul_Hossler]

The way I usually do this kind of thing; also mostly based on only 1 column

Only works for constants, and hopefully there are no logical values in the cells



Option Explicit


Sub test()
Dim v As Variant


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each v In Array("Z INFORMATION*", "More*")
Call Worksheets("Testing").UsedRange.Replace(v, True)
Next

On Error Resume Next
Worksheets("Testing").UsedRange.SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete
On Error GoTo 0


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

[snb]

In the string to split no values should contain spaces or asterisks.


split("aa bb cc dd ee")
or else the separator should be another character that any value doesn't contain.

split("aa bb_cc dd_ee ff","_")

[HTSCF Fareha]

Sorry snb, still not getting this.

Is this is in answer to my post #22?

I've tried this, but still get the #DIV/0! in the cells with the word 'INFORMATION'


For Each it In Split("*INFORMATION* *More*")
Sheet1.Cells.Replace "*" & it & "*", "=1/0"
Next
On Error Resume Next
Sheet1.Cells.SpecialCells(-4123, 16).EntireRow.Delete
On Error GoTo 0

[jolivanes]

Have you tried

For Each it In Split("INFORMATION More")

[HTSCF Fareha]

Yes, still no difference.

[HTSCF Fareha]

This is really strange. running this produces the same #DIV/0! in the cells with the word 'INFORMATION'.


For Each it In Split("INFORMATION")
Sheet1.Cells.Replace "*" & it & "*", "=1/0"
Next
On Error Resume Next
Sheet1.Cells.SpecialCells(-4123, 16).EntireRow.Delete
On Error GoTo 0

I really need this to pickup the words 'INFORMATION' and 'More', but ignore any errors if there aren't any.

[snb]

Apparently you have text to be removed several times in 1 row.


Sub M_snb()
On Error Resume Next

For Each it In Split("Information more")
For Each it1 In Sheet1.UsedRange.Columns
it1.Replace "*" & it & "*", "=1/0"
it1.SpecialCells(-4123, 16).EntireRow.Delete
Next
Next
End Sub

[HTSCF Fareha]

Brilliant! Sorted!!

Thank you! :thumb