getting the first and last value out of a non continious data set

[withy]

imagine you have column A

1
3
5

7
6
1
8


6
9
8

i want the numbers 1,5,7,8,6,8 to be printed into column b. make sense?

i am taking the starting and ending values of a non continuous data set. so far i have tried using a for next loop but i can only get the first value. i'm jsut stuck. ANY help is welcomed!

thanks in advance mike.

[DonkeyOte]

Welcome to the Board.

There are a few options open to you, one:

Public Sub FilterData()
Dim rngArea As Range
For Each rngArea In Columns(1).SpecialCells(xlCellTypeConstants, xlNumbers).Areas
    Cells(Rows.Count, "B").End(xlUp).Offset(1).Value = rngArea.Cells(1, 1).Value
    Cells(Rows.Count, "B").End(xlUp).Offset(1).Value = rngArea.Cells(rngArea.Rows.Count, 1).Value
Next rngArea
End Sub

The above assumes your values in Column A are numeric and not result of formulae, if they are result of formulae you will need to adjust the SpeicalCells line to xlCellTypeFormulas as opposed to xlCellTypeConstants

[royUK]

If your data starts in A1, try this

Option Explicit

Sub x()
    Dim rng    As Range
    Dim cl     As Range
    Dim Rw     As Long

    Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
    Set rng = rng.Resize(rng.Rows.Count + 1)
    Rw = 2
    Cells(1, 2).Value = Cells(1, 1).Value
    For Each cl In rng
        If IsEmpty(cl) Then
            Cells(Rw, 2).Value = cl.Offset(-1, 0).Value
            Cells(Rw + 1, 2).Value = cl.Offset(1, 0).Value
            Rw = Rw + 2
        End If
    Next cl
End Sub

Copy the code
Select the workbook in which you want to store the code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

[DonkeyOte]

Roy, I don't think that would work if you had variable blanks between range areas (as implied in sample data set) - I think using SpecialCells with Areas is probably a safer / quicker method.

[royUK]

I looked at your example & it would be better than mine, I never thought of using areas nor noticed the variable blanks.

[withy]

beautiful. just beautiful. marked as solved.

also, i'm jsut curious..... but i've been around forums for a long time and they all have a gen chat.... no gen chat here?

[royUK]

There's the Water Cooler which you can join

[withy]

HA HA, yea that makes sense..... even says off topic chat! guess i should probably get to sleep cause my brain is MELTING. thanks again guys.