• Excel Video Tutorials

How to Auto-Number or Renumber after Filter in Excel?

You could have observed that when we get a list of items by using the filter in tables, if they contain any serial number, they will not adjust, but we can make them in order by using the vba code; this can't be completed using the default operations provided by Excel. Read this tutorial to learn how you can auto-number or renumber after a filter in Excel.

AutoNumber or Renumber after Filter

Here we will insert a VBA module and then run it to complete our task. Let us see a simple process to understand how we can auto-number or renumber after filtering in Excel with the help of a VBScript application.

Step 1

Assume we have an Excel sheet with a table after the filter is applied, similar to the table shown in the image below.

Now to solve the problem, right-click on the sheet name and select view code to open the vba application, then click on insert and select module, then type the programme into the text box as shown in the below image.


Sub Renumber() 'Update by Nirmal Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = "Select the range" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) Set WorkRng = WorkRng.Columns(1).SpecialCells(xlCellTypeVisible) xIndex = 1 For Each Rng In WorkRng Rng.Value = xIndex xIndex = xIndex + 1 Next End Sub

Step 2

Now save the sheet as a macro-enabled sheet and click on F5 to pop up the window named "Select the range" and select the number range you want to update, as shown in the below image.

And, our final result will look something like the image below.


In this tutorial, we used a simple example to demonstrate how we can auto-number or renumber after filtering in Excel to highlight a particular set of data.