Get a list of sequential numbers down a column with starting number 1000 on a filtered list

[sspatriots]

I have a column that is filtered and random rows are filtered out for one reason or another based on what I'm doing. I've done this so that I can assign a sequential number to the values in column A that are not filtered out and am trying to copy the series down what is showing. I'm starting with number 1475. I've tried several methods I've found on line, but anything short of buying "Kultools", I'm stuck. Any ideas would be greatly appreciated.



Thanks, SS

 

[Paul15]

Hi, not sure if this is what you are looking for. I have a large spreadsheet which has a running sequential number, in column A. To get the number run to start at the right number, place your last number in a blank cell above where you want the numbers to start. The codec looks for this and then adds '1' to that number and so on. I use this on a user input form which calculates the next available number: last number + 1 and places it in the spreadsheet when data is submitted. hope this might help

'Add sequential number to column A

 With ThisWorkbook.Worksheets("your worksheet")
        Ref.Text = Application.Max(.Range("A:A")) + 1         Ref being my field name
    End With

1475 - no other data.

1476 - the code will provide the next sequential number

1477

 

[sspatriots]

Will this code skip the rows that are filtered off? That's what I would need it to do.

 

[sspatriots]

I found the code below, but not sure how to make it start with the first number in the selected range to equal 1000.

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

 

[Paul15]

Will this code skip the rows that are filtered off? That's what I would need it to do.

My guess is no. Even though rows may be filtered off, they still exist. The filter just hides then from view. Good luck with this, there will be an answer

 

[kvsrinivasamurthy]

Change this

    For Each xCell In xRg
        xVal = xVal + 1
        xCell = xVal
    Next

as

   For Each Xcell In xRg
        If Xcell.EntireRow.Hidden = False Then
        xVal = xVal + 1
        Xcell = xVal
        End If
    Next

 

[sspatriots]

Perfect. That worked