Fill in blank cells with data above 1

[Chumo82]

I was looking for a way either using straight excel or vba to populate blanks in a column with the information above. I need it for about 20000 records and it would be tedious to just keep copying and pasting. I hope this diagram better explains what I am looking to do.

Before:
1 6543212
2 BLANK
3 987321
4 BLANK
5 BLANK
6 BLANK

After:
1 6543212
2 6543212
3 987321
4 987321
5 987321
6 987321

Thanks in advance for any help anyone can give me.

 

[PHV]

How is the LAST row determined ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Chumo82]

Last row is determine by other columns of data. I kinda dumbed it down a little but I could live with having to manually put the last couple of entries in myself just not all 20000. I hope this helps better explain.

Chumo82

 

[Chumo82]

Actually I just found a simple solution on another forum. It doesn't even use vba so it can do for now. This is what I have found so far and it seemed to work pretty well.

If you have entered range from A2 to A500, then select them and
Do this...
1. Press F5 (go to)
2. Select blanks and press ok
3. now your cells having blanks will be selected with active cell as A3
4. enter formula =A2 and press Ctrl + Enter the formula will be entered
in all the cells.
5. Now again select the entire range A2 : A500 and copy
6. Paste Special - > Values...


Chumo82

 

[SkipVought]

Hi,

'assuming that these values are in column A
with activesheet.usedrange
  for each r in range(cells(.row, "A"), cells(.row+.rows.count-1, "A"))
    with r
      if .value = "" then
        .value = v
      else
        v = .value
      end if
    end with
  next
end with

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Chumo82]

Awesome! Thanks for the help. This make it very easy.

Chumo82