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.

Fill down sequence numbers skip hidden rows with formula

Fill down sequence numbers skip hidden rows with VBA code

Fill down sequence numbers skip hidden rows with Kutools for Excel

Fill down sequence numbers skip hidden rows with formula

Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!

Save 50% of your time, and reduce thousands of mouse clicks for you every day!

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:

Fill down sequence numbers skip hidden rows with VBA code

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 20151228
    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.

Fill down sequence numbers skip hidden rows with Kutools for Excel

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.

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:

Download and free trial Kutools for Excel Now !

Demo: Fill down sequence numbers skip hidden rows with Kutools for Excel

Excel Productivity Tools

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

•  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
•  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
•  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
•  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
•  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
•  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
•  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
•  Change the way you work now, and give you a better life immediately!

Read More Download Now Purchase

---

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

• Increases your productivity by 50% when viewing and editing multiple documents.
• Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
• Open and create documents in new tabs of same window, rather than in new windows.
• Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!

Read More Download Now Purchase