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

<div class="code-mirror  language-javascript" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;">Sub <span class="token function">Renumber</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
   'Update by Nirmal
   Dim Rng As Range
   Dim WorkRng As Range
   On Error Resume Next
   xTitleId <span class="token operator">=</span> <span class="token string">"Select the range"</span>
   Set WorkRng <span class="token operator">=</span> Application<span class="token punctuation">.</span>Selection
   Set WorkRng <span class="token operator">=</span> Application<span class="token punctuation">.</span><span class="token function">InputBox</span><span class="token punctuation">(</span><span class="token string">"Range"</span><span class="token punctuation">,</span> xTitleId<span class="token punctuation">,</span> WorkRng<span class="token punctuation">.</span>Address<span class="token punctuation">,</span> Type<span class="token operator">:</span><span class="token operator">=</span><span class="token number">8</span><span class="token punctuation">)</span>
   Set WorkRng <span class="token operator">=</span> WorkRng<span class="token punctuation">.</span><span class="token function">Columns</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">SpecialCells</span><span class="token punctuation">(</span>xlCellTypeVisible<span class="token punctuation">)</span>
   xIndex <span class="token operator">=</span> <span class="token number">1</span>
   For Each Rng In WorkRng
      Rng<span class="token punctuation">.</span>Value <span class="token operator">=</span> xIndex
      xIndex <span class="token operator">=</span> xIndex <span class="token operator">+</span> <span class="token number">1</span>
   Next
End Sub
</div>

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.