Need to make a macro in excel that finds blanks in

问题:

So I have a column with blanks randomly throughout...I need a macro that would select only the blanks, and then in those blank cells paste in the value of the cell above it. The select part is obviously easy, but I keep getting errors about 'too many continuations' when trying to fill the formula down into the blanks. I included a picture, the first column is a 'before' and the second is how I want the column to look after the application of the macro.

If the macro needs to create a second column or something that's fine too, as long as the end result looks like it does in the picture. Thanks!

Picture to illustrate.

回答1:

try,

sub fillblankfromabove()
    dim blnks as range
    with worksheets("Sheet1").Columns("E").Cells
        set blnks = .specialcells(xlcelltypeblanks)
        if not blnks is nothing then
            blnks.formular1c1 = "=r[-1]c"
        end if
        .value = .value
    end with
end sub

回答2:

Another way to do this is to select all cells that you want included in this process, press CTRL + G, select Special, then select 'Blank Cells'. This will select all blank cells within your selected range. Then enter =[cell above] and press CTRL + ENTER and it will enter that formula into all selected cells.