Returning Values from a table without gaps

[Nytro86]

Hello all.
I am trying to reference in a new Tab a list of all the items that hae an "X" against them I.E.

A B

Cats	X
Dogs
Parrots	X
Shrimp
Pandas	X

<tbody>
</tbody>

instead of getting
Cats
Blank
Parrots
Blank
Pandas


I am trying to get
Cats
Parrots
Panda

I have tried multiple lockups etc, and I am trying to have it so there is no filter/sorting required on the final page

any help is greatly appreciated

Kind regards

 

[fhqwgads]

maybe like this?

Sub new11()
Dim rowswithX As Range
Dim i As Integer

For i = 1 To 5
If Range("B" & i).Value = "X" Then
If rowswithX Is Nothing Then
            Set rowswithX = Rows(i)
        Else
            Set rowswithX = Union(rowswithX, Rows(i))
        End If
Else
End If
Next i
rowswithX.Copy Destination:=Worksheets("Sheet2").Range("A1")

End Sub

hope that helps

 

[JoeMo]

You can adapt the formula in C1 to enter it from a new tab. Copy it down to cover the list in col A.

Note the formula must be confirmed using ctrl+shift+enter.

Excel Workbook
	A	B	C
1	Cats	X	Cats
2	Dogs		Parrots
3	Parrots	X	Pandas
4	Shrimp
5	Pandas	X
Sheet8

 

[Peter_SSs]

Similar to JoeMo's but if you prefer without the Ctrl+Shift+Enter

Excel Workbook
	A	B	C
1	Cats	X	Cats
2	Dogs		Parrots
3	Parrots	X	Pandas
4	Shrimp
5	Pandas	X
6
List