How to fill down sequence numbers skip hidden rows in Excel?
Supposing, you have a filter data range which you want to fill down sequence numbers into the filtered blank cell as following screenshot shown. In this case, dragging the fill handle will not fill the cells correctly generally, this article, I will talk about some good tricks for solving this task in Excel.
To fill down sequence numbers only to the visible cells, a simple formula may help you, please do as follows:
1. Enter this formula: =SUBTOTAL(103,$B$3:$B3)(B3 is the visible cell which corresponding to the cell that you want to start filling the sequence number to ) into the first cell where you want to fill sequence numbers, A3, for this instance, and then press Enter key, you will get a number 1, see screenshot:
2. Then select cell A3, and drag the fill handle down to the cells that you want to fill the sequence numbers, see screenshot:
The following VBA code also can help you to fill the sequence numbers into only visible cells, please do as this:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Fill down sequence numbers skip hidden rows:
Sub FillValue() 'updateby Extendoffice Dim xRg As Range Dim xCell As Range Dim xTxt As String Dim xVal As Long Dim I As Long On Error Resume Next xTxt = ActiveWindow.RangeSelection.Address Set xRg = Application.InputBox("Please select the data range", "Kutools for Excel", xTxt, , , , , 8) Set xRg = xRg.SpecialCells(xlVisible) Debug.Print xRg.Address If xRg Is Nothing Then Exit Sub For Each xCell In xRg xVal = xVal + 1 xCell = xVal Next End Sub
3. Then press F5 key to run this code, and a prompt box is popped out to remind you select the data range that you want to fill, see screenshot:
4. And then click OK button, the sequence numbers have been filled into the visible cells only.
Above two methods only can help you to fill down the sequence numbers into the visible cells, if you want to fill sequence as AA-0001-BB, AA-0002-BB,AA-0003-BB…, the above methods will not work. But, please don’t worry about, Kutools for Excel’s Insert Sequence Number utility can help you finish it quickly and easily.
|Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.|
After installing Kutools for Excel, please do as follows:
1. Select the cells that you want to fill sequence strings.
2. Click Kutools > Insert > Insert Sequence Number, in the Insert Sequence Number dialog box, click New button, see screenshots:
3. In the expanded dialog box, please specify the settings as your needed into the left bottom section, at the same time, you can preview the sequence in the right bottom pane, see screenshot:
4. After finishing the settings, click Add button to save this new item into the above list box, see screenshot:
6. And then select the item you are created and click Fill Range button, the sequence strings have been filled into only the visible cells, see screenshot:
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 2 months agonone of these BS worked...The first one using formula, had skipped numbers and repeating other numbers... is there an easier way? FFS!
- To post as a guest, your comment is unpublished.· 2 years agoI have different worksheets in a workbook and I want to use VBA code for all worksheet at once .
Can anybody help me out.
In every sheet in A1 I have S.No. and I want it to be a Sequence number 1 2 3 4 .....
- To post as a guest, your comment is unpublished.· 2 years agoVERY THANKS
- To post as a guest, your comment is unpublished.· 2 years agoI have been manually adding serial numbers to sheets containing hundreds, if not thousands, of rows since the past few years. The above formula has literally save a whole lot of hours for me, for the rest of my life. Thank you, Op.
- To post as a guest, your comment is unpublished.· 3 years agoThis is not working. Instead of filling series, it's just copying first cell.
- To post as a guest, your comment is unpublished.· 5 months agoIt doesn't work for me either! I get 1 in the original cell and then 1 in every cell I copy the formula to.
- To post as a guest, your comment is unpublished.· 3 years agoAll of these steps above are way too complicated when I just want to double click the Fill Handle and Fill Down over hidden rows. Could Kutools give us a way to AutoFill using the Fill handle over filtered/hidden rows just using double click?
- To post as a guest, your comment is unpublished.· 3 years agoHello, Matt,
Thank you for your comment, Kutools does not support this feature so far, but, we will consider your suggestion.
- To post as a guest, your comment is unpublished.· 3 years agoHow can we use this solution with a custom starting number: Fill Down Sequence Numbers Skip Hidden Rows With VBA Code
- To post as a guest, your comment is unpublished.· 3 years agoThank you! This works!!! Really appreciate it!
Take Care and God Bless,