AB33 thank you for your response and once i understand arrays a little better i think i will like your solution the best. As i have to adapt this my real data and I am having issues doing that at the moment with your solution. It just feels cleaner though so i will keep working on it.

Leith Ross your solution is very simplistic but works and I am able to adjust it to my data set so thank you for your response. I do have concerns about the fact that it loops through for every blank cell of col a that is next to a non blank cell of col b doing nothing in each loop. I fear that with col a only being 4 long and col b being 75000 or more that it could be a resource hog.