Numbering a Range Using Autofilter

[Guest]

This is driving me crazy! I have autofiltered a range, but want to be able
to number that range from 1 to xxx each time I apply a different filter. But
even though I have only set the auto filter up on the specific columns it
hides the whole row that is being filtered. I have tried converting the
range to a list that also does not work, and nor does using the ROW function.
Any ideas?

 

[Don Guillett]

One way. This will re-number the visible cells in col A

Sub numbervisible()
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set myrng = Range("a2:a" & lr).SpecialCells(xlCellTypeVisible)
counter = 1
For Each c In myrng
c.Value = counter
counter = counter + 1
Next c
End Sub

 

[Debra Dalgleish]

You can use a formula to calculate the row number. For example, in
column A, enter the following:
=SUBTOTAL(3,$B$2:B2)

where column B contains no blank cells.
Copy the formula down to the last row of data.

Note: Don't use this formula in conjunction with the Data>Subtotal
feature. When you choose to Remove Subtotals, it deletes the lines that
contain a Subtotal formula.

 

[Guest]

Debra - thanks - does exactly what I want it to. Tanya