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.

Example

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.

Conclusion

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.

Pradeep Kumar

Updated on: 11-Jan-2023

851 Views

Kickstart Your Career

Get certified by completing the course

Get Started