Pulling Data from List if cell blank then move to next

As the title says really

Trying to pull data from a list and if the cell above is empty then pull the data from the cell below and so on........

I have some code for it..but its just a little bit too long and I can't use it

This is what i have so far, trying to simplify it as its way to long to fit in a singel cell....i did think about breaking it down in to collums of either 8 or 6 and having it in chunks that way...but if i did that then i would still have to eventually use this forumla below as i don't really want huge gaps within the form i'm doing

Please Login or Register  to view this content.

post an example of list and result you want/ but a simple way is as attached
stuff in col a extracted in col b

[page 1]
1 - 0
2 - 0
3 - 0
4 - 0
5 - e
6 - f



[page 2]
1 - if cell 1 on page 1 = 0 then return value in cell 2 in page 1 if cell 2 = 0 then return value in cell 3 page 1.......and so on
2 - if cell 1 contains value from page 1 in cell 2 then return value in cell 3 if cell 3 = 0 then return value in cell 4 page 1.........and so on
3 - as above
4 - as above
5 - as above



Think of it as a domino effect

so basicaly you just want to remove blank /0 cells from list
and put on sheet 2
use auto filter
filter on does not =0 and does not = blank
copy the rows displayed to sheet 2 (select entire row) anotherway with helper columns attached

if you have a list like that
number the list in a spare column 1 down to end
then select whole sheet then sort list all the blanks will go to bottom
delete blank rows
then sort again by the numbered column you created to put them all back in the right order with no blanks

if the blanks have been filled in how are they blanks?

windknife's works you could use that , i was trying to avoid arrays as they are not that obvious as to how they work, are difficult to remember.for newer users.
the one i posted will work so will the other. its just a question of how do you get 6 sheets ino one really and as they are changing in size that could be a bit awkward
it could probably be easily done with some vba code, finding the used range on each sheet and sending to a master without blanks.