Numbering a Range Using Autofilter
 

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?

Numbering a Range Using Autofilter
 

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


--
Don Guillett
SalesAid Software

"tanya" wrote in message
...
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?

Numbering a Range Using Autofilter
 

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 DataSubtotal
feature. When you choose to Remove Subtotals, it deletes the lines that
contain a Subtotal formula.

tanya wrote:
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?

--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Numbering a Range Using Autofilter
 

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

"Debra Dalgleish" wrote:

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 DataSubtotal
feature. When you choose to Remove Subtotals, it deletes the lines that
contain a Subtotal formula.

tanya wrote:
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?

--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html