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.
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
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)
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:
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.
Is there a way to exclude a merged cell that has text in it? This script works perfectly but I'm traversing a document that includes several horizontally merged cells that are made into title bars and I want to skip over/exclude those. Any help would be greatly appreciated!
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) Loop
BUT i want it to select range automatically instead of Set WorkRng = Application.Selection.
I purchased the Kutools so it would help me auto increment my invoice numbering but I am not seeing that feature in the add-in on the Excel tool bar. Maybe I'm not looking for the correct name but I am not finding how to insert this into my invoice template. Any out there
was wondering if there is anyway to alter the code where the range value will increase by one e.g. I am trying to use this for invoices and the number should go from NR000026489 to NR000026490, but using the code above with the modification given to JYOTHI it would increase from NR000026489 to NR0000264810. Any help would be greatly appreciated as I've spent ages trying different things but am not advanced enough in VB.
It is very helpful. but I need one solution, suppose I want to fill series into merged cells but the content of merged cell have data like TC_01, Is it possible to fill next merged cells like TC_02, TC_03etc.. If its possible it helps me a lot. Thanks in advance.