find blank cell and place a zero (0)

[Dummy Excel]

Morning All,

I have a spreadsheet which every now and then I need to add a new row (for a new store), although adding a new row leaves blank cells - I have recorded a macro to fill the blank cells, although was wondering if there is a simplier way of writing the code.

my code is:

    Range("D8").End(xlDown).Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "0"
    Range("e8").End(xlDown).Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "0"
    Range("f8").End(xlDown).Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "0"
    Range("g8").End(xlDown).Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "0"
    Range("h8").End(xlDown).Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "0"
    Range("i8").End(xlDown).Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "0"
    Range("j8").End(xlDown).Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "0"
    Range("k8").End(xlDown).Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "0"
    Range("l8").End(xlDown).Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "0"
    Range("m8").End(xlDown).Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "0"
    Range("n8").End(xlDown).Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "0"
    Range("o8").End(xlDown).Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "0"
    Range("p8").End(xlDown).Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "0"
    Range("q8").End(xlDown).Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "0"
    Range("r8").End(xlDown).Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "0"
    Range("s8").End(xlDown).Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "0"

thanks
Sam

 

[HalfAce]

Hello Sam,
You can replace all those lines of code with this single line:

Range("D8").End(xlDown)(2).Resize(, 16).Value = "0"

Hope it helps.

 

[Dummy Excel]

thanks this works great
two questions though about the formula:

1) what does (2) mean?
2) what does resize do?

thanks
Sam

 

[Dummy Excel]

I think I worked it out

End(xlDown)(2) is the same as End(xlDown).Offset(1, 0)?

Resize(, 16) places a 0 16 columns across?

 

[Scott Huish]

Resize(,16) resizes the range to 16 columns