Fill a range of cells with a value

[Tomsbdc]

Hi, I'm trying to create a macro in a cell that when clicked will clear the number values posted in range a2:a15 with the number 1; how do I do this? I'm not familiar with macros. Thanks

 

[hiker95]

Tomsbdc,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

So that we can get it right on the first try:

3. What is the worksheet name?

4. What cell are you going to click to activate the Worksheet_SelectionChange event?

 

[hiker95]

Tomsbdc,

Sample raw data with the Yellow cell is the cell to click on to activate the Worksheet_SelectionChange event code.

Excel 2007
	A
1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	12
13	13
14	14
15	15
16
Sheet1

After you click in/on cell A1, you get this:

Excel 2007
	A
1
2	1
3	1
4	1
5	1
6	1
7	1
8	1
9	1
10	1
11	1
12	1
13	1
14	1
15	1
16
Sheet1

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' hiker95, 05/25/2015, ME857104
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  Me.Range("A2:A15").Value = 1
  .ScreenUpdating = True
  .EnableEvents = True
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then select cell A1.

 

[Tomsbdc]

Windows 7, PC, Excel 2010

 

[Tomsbdc]

Awsome, thanks very much!

 

[Tomsbdc]

Hiker95 - Thanks very much, this worked very well and I was able to 'add' to the code to refill another area as well, making my sheet work even better. I appreciate the help!

Tomsbdc,

Sample raw data with the Yellow cell is the cell to click on to activate the Worksheet_SelectionChange event code.

Excel 2007

	A
1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	12
13	13
14	14
15	15
16

<colgroup><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

After you click in/on cell A1, you get this:

Excel 2007

	A
1
2	1
3	1
4	1
5	1
6	1
7	1
8	1
9	1
10	1
11	1
12	1
13	1
14	1
15	1
16

<colgroup><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' hiker95, 05/25/2015, ME857104
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  Me.Range("A2:A15").Value = 1
  .ScreenUpdating = True
  .EnableEvents = True
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then select cell A1.

 

[hiker95]

Tomsbdc,

Thanks for the feedback x 2.

You are very welcome. Glad I could help.

And, come back anytime.