Dynamically renumber filtered rows

[honkin]

I have a sheet which records football match results and there is a column which simply indicates the number of matches, starting from 1 onwards.

How is it possible to have that column be dynamic so that when the sheet is filtered, the first visible row counts as 1 and the the next visible 2 and so on?

I just want it to be able to be dynamic, so that all the numbering changes each time the filtering is changed

cheers

 

[footoo]

How to fill down sequence numbers skip hidden rows in Excel?

This article introduces some good tricks for filling down sequence numbers only to visible cells and skip hidden rows in Excel.

www.extendoffice.com

 

[kevin9999]

There may be more elegant ways of doing it, but this method does what you seem to be after. You haven't provided much information so the following is provided for demonstration purposes only. You can change the column and cell references to suit your sheet layout.
Basically, it uses a Worksheet_Calculate() private sub to trigger the event. There is a CountA() formula in the sample in cell C1 - you can put it anywhere on the sheet and hide the column it's in if need be.

The code which goes in the relevant sheet code area:

Private Sub Worksheet_Calculate()
    Dim ws As Worksheet, lr As Long, i As Long, c As Range
    Set ws = Sheet1
    Application.ScreenUpdating = False
    lr = ws.Cells(Rows.Count, 2).End(3).Row
    i = 1
    For Each c In ws.Range("A2:A" & lr)
        c.ClearContents
        If c.EntireRow.Hidden = False Then
            c = i: i = i + 1
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

Before applying a filter

test.xlsb
	A	B	C
1		filter	16
2	1	a
3	2	b
4	3	a
5	4	b
6	5	a
7	6	b
8	7	a
9	8	b
10	9	a
11	10	b
12	11	c
13	12	c
14	13	d
15	14	c
16	15	c
Sheet1

Cell Formulas
Range	Formula
C1	C1	=COUNTA(B1:B16)

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$B$1:$B$16	C1

after filtering for a & c

test.xlsb
	A	B	C
1		filter	16
2	1	a
4	2	a
6	3	a
8	4	a
10	5	a
12	6	c
13	7	c
15	8	c
16	9	c
17
Sheet1

Cell Formulas
Range	Formula
C1	C1	=COUNTA(B1:B16)

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$B$1:$B$16	C1

after filtering for b & d

test.xlsb
	A	B	C
1		filter	16
3	1	b
5	2	b
7	3	b
9	4	b
11	5	b
14	6	d
17
Sheet1

Cell Formulas
Range	Formula
C1	C1	=COUNTA(B1:B16)

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$B$1:$B$16	C1

 

[honkin]

There may be more elegant ways of doing it, but this method does what you seem to be after. You haven't provided much information so the following is provided for demonstration purposes only. You can change the column and cell references to suit your sheet layout.
Basically, it uses a Worksheet_Calculate() private sub to trigger the event. There is a CountA() formula in the sample in cell C1 - you can put it anywhere on the sheet and hide the column it's in if need be.

The code which goes in the relevant sheet code area:

Private Sub Worksheet_Calculate()
    Dim ws As Worksheet, lr As Long, i As Long, c As Range
    Set ws = Sheet1
    Application.ScreenUpdating = False
    lr = ws.Cells(Rows.Count, 2).End(3).Row
    i = 1
    For Each c In ws.Range("A2:A" & lr)
        c.ClearContents
        If c.EntireRow.Hidden = False Then
            c = i: i = i + 1
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

Before applying a filter

test.xlsb
	A	B	C
1		filter	16
2	1	a
3	2	b
4	3	a
5	4	b
6	5	a
7	6	b
8	7	a
9	8	b
10	9	a
11	10	b
12	11	c
13	12	c
14	13	d
15	14	c
16	15	c
Sheet1

Cell Formulas
Range	Formula
C1	C1	=COUNTA(B1:B16)

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$B$1:$B$16	C1

after filtering for a & c

test.xlsb
	A	B	C
1		filter	16
2	1	a
4	2	a
6	3	a
8	4	a
10	5	a
12	6	c
13	7	c
15	8	c
16	9	c
17
Sheet1

Cell Formulas
Range	Formula
C1	C1	=COUNTA(B1:B16)

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$B$1:$B$16	C1

after filtering for b & d

test.xlsb
	A	B	C
1		filter	16
3	1	b
5	2	b
7	3	b
9	4	b
11	5	b
14	6	d
17
Sheet1

Cell Formulas
Range	Formula
C1	C1	=COUNTA(B1:B16)

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$B$1:$B$16	C1

Ta Kevin, but think I found exactly what I am after. I assumed something like SUBTOTAL or AGGREGATE may be the answer and finally found it.

=SUBTOTAL(3,$AG$21:AG21)

This is the baby which handles it.

And the below now takes care of the P&L dynamically as well

=SUBTOTAL(109,$I$21:I21)

Cheers

 

[kevin9999]

Ta Kevin, but think I found exactly what I am after. I assumed something like SUBTOTAL or AGGREGATE may be the answer and finally found it.

=SUBTOTAL(3,$AG$21:AG21)

This is the baby which handles it.

And the below now takes care of the P&L dynamically as well

=SUBTOTAL(109,$I$21:I21)

Cheers

I knew there had to be a simpler method ? Thanks for letting me know ?

 

[Peter_SSs]

.. and finally found it.

=SUBTOTAL(3,$AG$21:AG21)

This is the baby which handles it.

I hope that 'finally' didn't take you a long time after post #2 above as the solution was right there at the beginning of that link @footoo provided.

Thanks for letting me know ?

footoo had already let you know as per my comment above.