# How to use Excel Formulas to Fill Sequence Numbers Skipping Hidden Rows – 3 Methods

The dataset showcases Serial No, Month, Employee ID, and Salary. Rows 10, 11 and 12 are missing. To fill the sequence numbers in column B skipping hidden rows:

### Method 1 – Using the AGGREGATE Function to Fill Sequence Numbers Skipping Hidden Rows in Excel

STEPS:

• Select B5
• Enter the following formula:
`=(AGGREGATE(2,7,\$B\$4:B4))+1`

Formula Breakdown

2 and 7 in (AGGREGATE(2,7,..) represent the nested SUBTOTAL and AGGREGATE function ignoring the hidden rows and error values in \$B\$4:B4.Â 1 is added to start the sequence numbers.

• Press ENTER.

You will see 1 in B5.

• Drag down the Fill Handle to see the result in the rest of the cells.

• This is the output.

### 2. Fill Down Sequence Numbers Skip Hidden Rows in Excel Using SUBTOTAL Function

Â STEPS:

• Select B5.

• Enter the following formula:
`=SUBTOTAL(103,\$C\$5:C5)`
Formula Breakdown

SUBTOTAL(103,\$C\$5:C5) represents the total numeric value in \$C\$5:C5. The first argument 103 refers to the COUNTA function:Â it counts numbers ignoring the hidden rows and error values.

• Press ENTER.

You will see 1 in B5.

• Drag down the Fill Handle to see the result in the rest of the cells.

• This is the output.

### Method 3 – Using a VBA Code to Fill a Sequence of Numbers Skipping Hidden Rows

STEPS:

• Select B5:B16.

• Go to the Developer Tab >> Select Visual Basic

• Select Sheet4 (VBA) >> Go to Insert >> Select Module

Enter the code in the Module.

``````Sub FillDownSequenceNumbers()
Dim Xrng As Range
Dim Ycell As Range
Dim ExcelTxt As String
Dim Xvl As Long
Dim I As Long
On Error Resume Next
Set Xrng = Application.InputBox("Select Range", "Fill Down Sequence Numbers", ExcelTxt, , , , , 8)
Set Xrng = Xrng.SpecialCells(xlVisible)
If Xrng Is Nothing Then Exit Sub
For Each Ycell In Xrng
Xvl = Xvl + 1
Ycell = Xvl
Next
End Sub``````

• Press F5 to run the code. In the InputBox , click OK.

• This is the output.

Read More: How to Autofill Dates in ExcelÂ

Practice here.

## Related Articles

<< Go Back to Autofill Numbers |

Get FREE Advanced Excel Exercises with Solutions!
Foyez Alam

FOYEZ ALAM is a leather engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SPSS, STATA, Deep Neural Networks, and Machine Learning. Armed with a B.Sc in the Institute of Leather Engineering & Technology, University of Dhaka, he's shifted to become a content developer. In this role, he crafts technical content centred... Read Full Bio

1. When I enter the aggregate the fill handle doesn’t work!

• Hello WILL,
Thanks for your feedback. There are some reasons that are why you may have faced the problem. You can solve it by following the steps:

1. Maybe your Fill Handle tool is deactivated. To activate it, Click File > Options > Advanced > Enable fill handle and cell drag and drop.

2. The AGGREGATE function can work only for vertical ranges, not for horizontal ranges. So always apply it for vertical ranges and then the Fill Handle should work.

3. The AGGREGATE function is available since 2010, so if you are using an older version of Excel then it won’t work.

If the above solutions fail to rescue you then your issue is quite particular and that is difficult to find out without the file. So if you share your file with us then we hope, we could provide you with the exact solution.

2. I have chosen the SUBTOTAL formula like this
=SUBTOTAL(103,\$B\$23:B23)*10 because I wanted it to be 10,20,30…..
and it worked for me great.

thanks

• Hello Shaul Bel,

Thanks for your appreciation. You’re welcome and I’m really glad to hear that the SUBTOTAL formula worked well for you in achieving the desired results. If you have any more questions or need further assistance with formulas or anything else, feel free to ask!

Regards
ExcelDemy

Advanced Excel Exercises with Solutions PDF