CODEWITHSUNDEEP
vbaexcel
Tevin
Tevin

Reputation: 35

Fill blanks with top cell

Given the following code, I would like to understand how it works:

    Columns("A:A").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"

So what it currently does is it takes the top cell of the row and auto fills it down with formula =A1 relative to that cell on all blank cells below it.

However it stops at the next cell that contains a value and does the same treatment down all the cells below it till the next.

How does this work exactly? How does vba knows to take the formula from the cell above the blanks and not just from the first cell of the column?

Much help is appreciated!

Upvotes: 0

Views: 129

Answers (1)

girlvsdata
girlvsdata

Reputation: 1644

This is how the code works:

Select the entire column A

Columns("A:A").Select

Now select just the blank cells in column A

Selection.SpecialCells(xlCellTypeBlanks).Select

Now for every cell we've selected (all the blank cells in column A), input a formula. The formula roughly equates to: "Equals to the cell immediately above this cell."

Selection.FormulaR1C1 = "=R[-1]C"

The formula is using a relative cell reference, and it literally translates to "Equals Row-1, same Column."

Upvotes: 1

Related Questions