AUTO FILTER - can manual row numbers re-number themselves?

[itr674]

I have a project where I have numbered the rows starting at A10 down to A1000. So the numbers go from 1 to 1010. If I auto filter for a certain office and it pulls up 50 rows would it be possible for the row numbers to re-number themselves in column A to show 1 thru 50 for that particular office--and then when the auto filter is removed the numbers would revert back?

 

[TheBuGz]

Re: AUTO FILTER - possibel to have row numbers "re-numb

I would also be looking Forward to the same thing as desired by em

 

[itr674]

Re: AUTO FILTER - can manual row numbers re-number themselve

Throwing this back out there...

 

[Zack Barresse]

Re: AUTO FILTER - can manual row numbers re-number themselve

hi there,

sounds like a vba solution may work here, and i can't give you that. but if you want to restructure your data, and assign a count to each individual *account* which adds everytime another entry is produced of the same likeness, maybe something like this...

Book14.xls
	A	B	C	D
1	1	Alpha
2	1	Bravo
3	1	Delta
4	1	Echo
5	2	Alpha
6	3	Alpha
7	4	Alpha
8	2	Bravo
9	5	Alpha
10	6	Alpha
11	3	Bravo
12	1	Charlie
13	2	Delta
14	4	Bravo
15	7	Alpha
Sheet5

formula in B2 is:
=IF(ROW()=1,1,IF(B2="","",IF(B2=B1,A1+1,COUNTIF(B$1:B2,B2))))

copy down as far as wanted.

i hope this helps. post back with results.

 

[TheBuGz]

Still Unanswered

 

[itr674]

Re: AUTO FILTER - can manual row numbers re-number themselve

firefytr - that's not exactly what I wanted. Wanted the entire list numbered sequentially so I printed the full list everything would be numbered or if I auto filtered and then printed just that list it would be numbered sequentially...

 

[DRJ]

Try a UDF

Function xCount(Rng As Range)

Dim Counter     As Long
Dim Cell        As Object

    Application.Volatile
    For Each Cell In Rng
        If Cell.RowHeight > 0 Then
            Counter = Counter + 1
            Else
        End If
    Next
    xCount = Counter

End Function

The in A1 put

=xcount($A$1:A1)

and fill down

 

[Legacy 21301]

Re: AUTO FILTER - can manual row numbers re-number themselve

Can't think of a way to do it automatically when the filter is changed (hiding/unhiding rows cannot directly trigger an event procedure).

But you could put the following code either in a Worksheet_SelectionChange procedure, or in a macro attached to a button :-

Dim rng As Range, cell As Range, number&
On Error GoTo e
Set rng = Range([A11], [A65536].End(xlUp)).SpecialCells(xlCellTypeVisible)
Application.ScreenUpdating = False
For Each cell In rng
     number = number + 1
     cell = number
Next
Application.ScreenUpdating = True
e: End

NOTE : With DRJ's custom function, after hiding or unhiding rows with the filter, you would need to press Ctrl+Alt+F9 to make the function recalculate.

 

[itr674]

Re: AUTO FILTER - can manual row numbers re-number themselve

DRJ - plugged in your function and it worked.

Ponsy Nob. - thanks for you code also.

Thanks to both for your solutions, I will be using at least one of them--althought still hoping for a formula fix...

 

[Zack Barresse]

don't think a formula fix is possible in your situation, except by Jacob's UDF function.