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

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.

Welcome to the Board.

There are a few options open to you, one:

Please Login or Register  to view this content.

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

If your data starts in A1, try this

Please Login or Register  to view this content.

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

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.

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

There's the Water Cooler which you can join