Fill blank cells from non blank cell below

[Idrees]

Hi

Most forums I searched, I was able to find a solution for filling a value above in the non-blank cells below. But my requirement is to do the other way round, i.e. keep filling the non-blank cells with the first value found below those non blanks.

Example (below is my original data):
Head1 Head2
5
X 3
5
4
3
Y 7

In the above example, I would like to fill row 2 with X and rows 4-6 with Y so the result will look like:

Head1 Head2
X 5
X 3
Y 5
Y 4
Y 3
Y 7

Btw, I preferably need a VBA solution as I migh
Thanks for your help in advance.

Kind regards
Idrees

 

[p45cal]

try this:

Sub blah()
With Range("A:A")
  .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[1]C"
  .Value = .Value
End With
End Sub

 

[Idrees]

Wow... brilliant... Thanks p45cal

It works like a gem with the sample data I provided. The only issue is it is not working in my actual data and I think it is because even though the cells look blank they are not for some reason.

If you have an answer as how can I convert those to blanks, please let me know.

Sorry I have attach something before I can post a reply or create a new thread, don't know why.

Thanks and regards

 

[p45cal]

and I think it is because even though the cells look blank they are not for some reason.

…therein the nub of the problem; if you don't know, I certainly can't! Attach an excel file with such cells.

 

[Idrees]

Thanks p45cal.

I have figured it out and as I mentioned, it was the cells that looked blank but are not because of the way it was copied. I have changed the code to copy in such a way that it is blank for blank cells.