How to define a range based on the cell value

[largeselection]

Hi,

I'd like to define a range and include only the entries that have a certain code in the cell next to them.

So my data looks like this:

A B C D E
4 x
3 y
2 x
1 y
5 x

So I need to define a range in vba and have the range be equal to the collection of cells in column A that have an "x" as their value in column B.

So my range would be ("A1", "A3", and "A5")

How can I put this into vba to say set the range = all cells where column B's value is "x", but offset (0,-1).

That would be a HUGE time saver and help!

Thanks

 

[Domenic]

Maybe something like this...

Option Explicit

Sub test()

    Dim NewRng As Range
    Dim LastRow As Long
    Dim i As Long

    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    For i = 1 To LastRow
        If LCase(Cells(i, "B").Value) = "x" Then
            If NewRng Is Nothing Then
                Set NewRng = Cells(i, "A")
            Else
                Set NewRng = Union(NewRng, Cells(i, "A"))
            End If
        End If
    Next i
    
    [COLOR="SeaGreen"]'Your code here[/COLOR]
                
End Sub

 

[largeselection]

Thanks so much for your help!

I was looking for the "union" idea that you demonstrated.

I adjusted it in my code so that I can use a Select Case structure, but I did not know how to join the different selections and Union did just the trick.

Thanks!

 

[creedsthoughts]

This awesome code (with slight modification for my situation) helped reduce processing time from 3 min to 8 sec - thanks!!

Maybe something like this...

Option Explicit

Sub test()

    Dim NewRng As Range
    Dim LastRow As Long
    Dim i As Long

    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    For i = 1 To LastRow
        If LCase(Cells(i, "B").Value) = "x" Then
            If NewRng Is Nothing Then
                Set NewRng = Cells(i, "A")
            Else
                Set NewRng = Union(NewRng, Cells(i, "A"))
            End If
        End If
    Next i
    
    [COLOR=SeaGreen]'Your code here[/COLOR]
                
End Sub

 

[Domenic]

That's great to hear!

Cheers!