Info

Using the specialCells property to select all the blanks in this range is one way to quickly fill in all the blank region cells with the region found above them: Sub FillIn()

Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1 _ = "=R[-1]C"

Range("A1").CurrentRegion.Value = Range("A1").CurrentRegion.Value End Sub

In this code, Range("A1").CurrentRegion refers to the contiguous range of data in the report.The .SpecialCells property returns just the blank cells in that range.Although you can read more about R1C1 style formulas in Chapter 6,"R1C1 Style Formulas,"this particular formula fills in all the blank cells with a formula that points to the cell above the blank cell.The second line of code is a fast way to simulate doing a Copy and then Paste Special Values. The result is shown in Figure 3.11.

Figure 3.11

After running the macro, the blank cells in the region column have been filled in with data from above.

Figure 3.11

After running the macro, the blank cells in the region column have been filled in with data from above.

0 0

Post a comment