1

Assume an Excel sheet contains the following values in a random column:

VARIABLE X
AbbA
AddA
bbAA
ccbb
KaaC
cccc
ddbb
ccdd
BBaa
ddbB
Bbaa
dbbd
kdep
mCca
mblp
ktxy

Now the column should be searched for several words and word-phrases at the same time, for example the following:

(1) "bb"

(2) "cc"

(3) "d"

I put the target strings in an array:

Dim searchFor As String
Dim xArr 
searchFor = "bb/cc/d"
xArr = Split(searchFor , "/")

Also assume it does not matter if "bb" is in small letters or big letters (not case sensitive in this case). For the other cases it is case sensitive. At the end I would like to select the respective target cases in terms of their associated rows. Please also note that I would like to include cases in the selection, where the target string (e.g. "bb") is part of a word (e.g. "dbbd").

If possible, ignore the column title ("VARIABLE X) for searching/filtering as well as in the final selection of values.

How can this be done in VBA using (1) filters and/or using (2) regular loops? Which way would you recommend?

Improve this question
12
  • 1
    Can you please show us what have you tried? Commented May 21, 2015 at 20:51
  • @SiddharthRout I have tried to apply a solution (by u): stackoverflow.com/questions/11631363/… for a long while but I cannot get the code to work with multiple criteria nor can I find the answer to the problem anywhere else Commented May 21, 2015 at 20:56
  • well you could try adding the failing code ;) Commented May 21, 2015 at 20:58
  • LOL.. I was exactly thinking of that thread... If you have huge data in Excel then Autofilter is the best way else you can use loops as well. Commented May 21, 2015 at 20:59
  • What is it that you want to do after finding them? If you want to just identify the cells then use .Find and .Findnext as shown Here Commented May 21, 2015 at 21:00

3 Answers 3

Reset to default
4

AbBa should be either selected or deleted. I am trying to ID 'wrong cases' by applying this routine.

Further to my comments, here is an example using .Find and .FindNext

My Assumptions

  1. We are working with Col A in Sheets("Sheet1")
  2. My Array is predefined. You can use your array.

In the below example, I am coloring the cells red. Change as applicable.

Sub Sample()
    Dim MyAr(1 To 3) As String
    Dim ws As Worksheet
    Dim aCell As Range, bCell As Range
    Dim i As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    MyAr(1) = "bb"
    MyAr(2) = "cc"
    MyAr(3) = "d"

    With ws
        '~~> Loop through the array
        For i = LBound(MyAr) To UBound(MyAr)
            Set aCell = .Columns(1).Find(What:=MyAr(i), LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)

            If Not aCell Is Nothing Then
                Set bCell = aCell
                aCell.Interior.ColorIndex = 3

                Do
                    Set aCell = .Columns(1).FindNext(After:=aCell)

                    If Not aCell Is Nothing Then
                        If aCell.Address = bCell.Address Then Exit Do
                        aCell.Interior.ColorIndex = 3
                    Else
                        Exit Do
                    End If
                Loop
            End If
        Next
    End With
End Sub

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Working perfectly as expected. Thank you!
That's Awesome. I better hit the sack now! it's already 4:05 am on this part of the planet :P
God I miss Python when working with VBA... if any([find in 'your string' for find in ['a','b','c'...]])
1

Start with your data in column A, this:

Sub qwerty()
   Dim i As Long, N As Long
   N = Cells(Rows.Count, "A").End(xlUp).Row

   For i = 2 To N
      t = LCase(Cells(i, 1).Text)
      If InStr(t, "bb") + InStr(t, "cc") + InStr(t, "d") = 0 Then
         Cells(i, 1).EntireRow.Hidden = True
      End If
   Next i
End Sub

will hide the miscreants:

enter image description here

AutoFilter can be tough with more than two options.

Improve this answer

2 Comments

thanks for the solution. I wonder, does that work case sensitive? So for example can I search for *bb* by doing that? (At a sidenote I did not want to hide the rows but rather select them, but I know how to fix this)
Alright I have never used this function before. Still your solution does not work correctly, as it did not delete for example row 4 (contains "bb") or row 11 (contains "bB" which I wanted to be equal to "bb").
1

One way to delete these would be to use the Advanced Filter. Of course, you don't need VBA for this, but using VBA:

Option Explicit

Sub FilterByStrings()
    Dim rData As Range
    Dim rFiltered As Range
    Dim rCriteria As Range
    Dim vStrings As Variant, critStrings() As Variant
    Dim I As Long

vStrings = VBA.Array("bb", "cc", "d")

Set rData = Range("a1", Cells(Rows.Count, "A").End(xlUp))
Set rFiltered = Range("B1")
Set rCriteria = Range("c1")

'Add the wild cards and the column headers
ReDim critStrings(1 To 2, 1 To UBound(vStrings) + 1)
For I = 0 To UBound(vStrings)
    critStrings(1, I + 1) = rData(1, 1)
    critStrings(2, I + 1) = "<>*" & vStrings(I) & "*"
Next I

'criteria range
Set rCriteria = rCriteria.Resize(UBound(critStrings, 1), UBound(critStrings, 2))
rCriteria = critStrings

rData.AdvancedFilter Action:=xlFilterCopy, criteriarange:=rCriteria, copytorange:=rFiltered
rCriteria.EntireColumn.Clear

End Sub

If you want to return the cells that match those strings, you would set up the criteria range differently, removing the <> and having the criteria in a single column, rather than in adjacent rows.

Improve this answer

1 Comment

Oh very interesting, thanks for the entry. I didnt think of advanced filters.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.