How could we fill series numbers into a list of different size merged cells in Excel? First in our mind is to drag the Autofill handle to fill the merged cells, but, in this case, we will get the following warning message, and can’t fill the merged cells.
Since the fill handle does not support the merged cells, here, I can talk about some other tricks for solving this problem.
Auto number / fill merged cells with VBA code
Auto number / fill merged cells with VBA code
How to be more efficient and save time when using Excel?
The following VBA code can help you to number the selected merged cells quickly. Please do as follows:
1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Auto number merged cells
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| Sub NumberCellsAndMergedCells() 'Update 20141028 Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type: = 8) Set WorkRng = WorkRng.Columns(1) xIndex = 1 Set Rng = WorkRng.Range("A1") Do While Not Intersect(Rng, WorkRng) Is Nothing Rng.Value = xIndex xIndex = xIndex + 1 Set Rng = Rng.MergeArea.Offset(1) LoopEnd Sub |
3. Then press F5 key to run this code, and a prompt box will pop out to let you select the merged cells you want to fill, see screenshot:
4. After selecting the merged cells, and click OK, now, your selected merged cells have been filled with sequential numbers, see screenshot:
[source]
