Thread: Transpose column to row but remove / delete certain values

Hi,

I have seen variations of this discussed, but I didn't seem to find a discussion of my specific problem (and if I've somehow missed it, please point me in the direction of it).

So, I have a column of values that I would like to transpose to a row. That's easy enough. However, the issue is that I would like to remove (delete) certain values such that they do NOT show up in my transposed row.

For instance, my values are of the form:
1XX
1XX
2XX
2XX
2XX
3XX
3XX
4XX
4XX
4XX
4XX

Where, "XX" can be any number from 0-9.

I want to transpose that, but, let's say, remove all values starting w/ 3, so the result would be:

1XX 1XX 2XX 2XX 2XX 4XX 4XX 4XX 4XX

Now, if it's easy to do it w/ a "hard coded" value of 3, I will be happy w/ that.

However, what would be "nice" is to have the freedom to choose which beginning number I want to remove (to have a more robust macro).

So, for instance, if I want it to remove all values starting with 1, or 2, or 3, or 4, etc. I'm assuming that would require some sort of user interface window to have the user type in the starting number (in the example above, it would be 3), and then the code would remove all cells which start with 3.

If that is too difficult, the hard coded version will do exactly what I need.

Also, if something like this can be achieved just be using Excel's built-in functionality without any programming, that would be great too (just point me to the proper function name & I'll look up the help for it).

TIA,
Rob

In this you specify the number to exclude, the starting range and first cell where results should go:

Sub x()

Dim n As Long, rIn As Range, rOut As Range, v, w, i As Long, j As Long

With Application
    n = .InputBox("Number to exclude?", Type:=1)
    Set rIn = .InputBox("Starting range?", Type:=8)
    Set rOut = .InputBox("Output cell?", Type:=8)
End With

v = rIn.Value
ReDim w(1 To UBound(v))

For i = LBound(v) To UBound(v)
    If Left(v(i, 1), 1) <> n Then
        j = j + 1
        w(j) = v(i, 1)
    End If
Next i

rOut.Resize(, j) = w

End Sub