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
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
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.
| 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:
Download and free trial Kutools for Excel Now !
Demo: Fill down sequence numbers skip hidden rows with Kutools for Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
