CODEWITHSUNDEEP
vbaexcelms-office
user2966174
user2966174

Reputation: 21

Excel-Macro to fill serial numbers to filtered data

I have 3 columns A,B & C. The data is in B & C. I filter Column C to show "Unique records only". Now I want to add serial numbers to this filtered data in Column A. Be advised that the number of rows in the list (and therefore the filtered list) is not fixed. I know the function (=SUBTOTAL(3,$B$1:B2)-1 ) but this requires manual intervention. I also found the VBA code that works on an unfiltered list: 

Sub FillSerialNumbers()
  With Range("A3:A" & Range("B" & Rows.Count).End(xlUp).Row)
    .Cells(1, 1).Value = 1
    .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Trend:=False
  End With
End Sub

However I'm unable to implement it on a filtered list. Any help would be appreciated.

Upvotes: 1

Views: 4878

Answers (2)

Automate This
Automate This

Reputation: 31364

Here is another way

  Set rRng = Range("A3:A" & Range("B" & Rows.Count).End(xlUp).Row)
  Dim cntr As Integer: cntr = 1

  For Each cell In rRng
    If cell.EntireRow.Hidden = False Then
        Cells(cell.Row, 1).Value = cntr
        cntr = cntr + 1
    End If
  Next

Upvotes: 1

tigeravatar
tigeravatar

Reputation: 26650

As a formula solution, you could try, in cell A3 and copied down:

=IF(AND(COUNTIF(C$3:C3,C3)=1,C3<>""),max(A$2:A2)+1,"")

Upvotes: 0

Related Questions