find blank cell and place a zero (0)

D

Dummy Excel

Well-known Member
Joined
Sep 21, 2005
Messages
1,004
Office Version
  1. 2019
  2. 2010
  3. 2007
Platform
  1. Windows
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:
Code: 
    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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Hello Sam,
You can replace all those lines of code with this single line:
Code: 
Range("D8").End(xlDown)(2).Resize(, 16).Value = "0"

Hope it helps.
 
Upvote 0
thanks this works great
two questions though about the formula:

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

thanks
Sam
 
Upvote 0
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
974
Joe4
Joe4
R
  • Question
Autofill from Last Row
Replies
3
Views
610
RoryA
RoryA
L
  • Locked
  • Question
Help Cleaning Up Code
Replies
2
Views
332
RoryA
RoryA
H
  • Solved
VBA copy-paste down column, without stopping at non-blank cells.
Replies
3
Views
441
Tetra201
Tetra201
N
  • Question
Changing recorded macro when trying to format a sheet - Need dynamic selection of cells
Replies
2
Views
459
nidhinair22
N

Forum statistics

Threads
1,215,005
Messages
6,122,661
Members
449,091
Latest member
peppernaut

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top