Fill blank cells with 0 or alternative number

[Spellbound]

Hi all

I have a worksheet which is basically just for data entry; however some entries only require minimal data, leaving the cells in 3 columns blank.

I have just come across this code in another post (courtesy of Simon Lloyd) which is ideal for general purposes.

Please Login or Register  to view this content.

Ideally I would like to customise this code to do the following:

1) Restrict the range selection for use with columns D / E / F on Sheet1 for example, so that the range selection stops at the last filled cell in column C.

2) It may be necessary to change the zero value in either D / E / F at a later date, so I would like the code to have individual values for each column, if that makes sense.

3) This is not essential but can the code be set to run on a row by row basis for that specific worksheet. In other words, if there is no entries in D / E / F of row 25, when you move down to row 26, the zero input is automatically filled in rather than invoking a macro at the end.

Please note my knowledge of VBA is virtually zero!

TIA ...spellbound

[mrice]

Try

Please Login or Register  to view this content.

for your first two questions.

[Spellbound]

Hi Martin

Thanks for your speedy response, it seems to be working fine.

I have set all the MyCell.Value to zero for the time being.

I wanted to make the Macro specific to one worksheet, which I did by just adding the code via the worksheet tab with no apparent problems.

In order to make the Macro connection with that worksheet, is there any of amending the Macro name as I could not see any way of doing this.

At the moment VBA has assigned the name as Sheet5.Fill_Blanks to the macro, whereas I would prefer to call it Input2.Fill_Blanks which is the name of the actual worksheet.

Only a small point but it helps with identification.

spellbound

[mrice]

Worth trying changing the name of the sheet. In the VBA editor, open the properties window (F4) and change the name which is in the top row.