VBA Code to assign sequence serial numbers to cells in filtered excel sheet | Macro to assign sequence serial numbers to cells in filtered excel sheet

on

VBA Code to assign sequence serial numbers to cells in filtered excel sheet | Macro  to assign sequence serial numbers to cells in filtered excel sheet


Step 1: Open Filtered Data Excel Sheet. 

Step 2: Press Alt + F11 on keyboard and you can see Microsoft Visual basic for applications window.

Step 3: Go to Insert Menu and select Module.

Step 4: In empty module, copy and paste below VBA code and save. (before save you must save your sheet as Macro - Enabled sheet)

Step 5: Go to the Filtered Data sheet, select cells where you want to give serial sequence numbers and go to Developer Menu and click on macro. You ill see Find Value Macro Macro, click and run the macro.

VBA Code: 


Sub FillValue()
'updateby Extendoffice 20151228
    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

Comments

Post a Comment