Look for 2 different criteria in same column

hugletank · Jun 10th 2013

I am looking to create a loop that will look specific text in column A. Once found, it will look for the first cell above that cell, that contains another specific text. There maybe any number of rows between the 2 cells, and there can be several occurrences of these through column A

So for example, say cell A1 contains text 'A'. Cell C1 contains text 'B'. The loop first looks for 'B', and once found then looks for 'A' in a cell above it. I then need to use the number of rows between the 2 to resize a range

**pike** · Jun 13th 2013

Re: Look for 2 different criteria in same column

Hi
You can store the row values found
Then use them

Code

Dim i As Long
    Dim Aholder As Long
    Dim Bholder As Long
    For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
        If Cells(i, "A").Value = "B" Then
            Bholder = i
        End If
        If Cells(i, "A").Value = "A" And Bholder <> vbNullString Then
            Aholder = i
          Range("A" & Aholder & ":A" & Bholder).Delete
        Exit For
        End If
    Next i

Display More

Look for 2 different criteria in same column

Participate now!

Share