Numbering after using autofilter

[Matt Pennington]

I have used autofilter to refine data now I need to number the rows - can
this be done automatically or does it need to be done after each filter is
selected?

 

[Stefi]

I would copy the autofiltered range to a separate sheet where numbering is
automatically done!

Regards,
Stefi


„Matt Pennington†ezt írta:

 

[Mike H]

Hi

After filtering select the rightmost column of the filtered range and run
this macro. Note it numbers rows in the row to the right of the selection
which could overwrite data

Sub Sonic()
Dim rngCell As Range
x=1
For Each rngCell In Selection
If rngCell.RowHeight <> 0 Then
rngCell.Offset(, 1).Value = x
x = x + 1
End If
Next rngCell
End Sub

Mike

 

[Glenn]

I have used autofilter to refine data now I need to number the rows - can
this be done automatically or does it need to be done after each filter is
selected?

One way:

Assuming you have headers in row 1 and data in row 2:1000 and columns B, put
the following in A2 and copy down as needed:

=SUBTOTAL(3,$B$2:B2)

 

[Matt Pennington]

Genius Glenn - this is perfect thanks alot!

One way:

Assuming you have headers in row 1 and data in row 2:1000 and columns B, put
the following in A2 and copy down as needed:

=SUBTOTAL(3,$B$2:B2)

 

[Matt Pennington]

Mike thanks for your help but could you also tell me how to run the macro?

 

[T. Valko]

Just make sure you put the formula column *outside* of the filtered range.
Otherwise, you could get incorrect filtered data.

 

[Glenn]

Just make sure you put the formula column *outside* of the filtered range.
Otherwise, you could get incorrect filtered data.

Not sure what you mean. I didn't see any problems...

 

[T. Valko]

Try this:

Enter this column Header in B1: Name

Enter this data in B2:B6: 1;2;3;1;4

Enter this formula in A2 and copy down to A6:

=SUBTOTAL(3,B$2:B2)

Now, apply the filter...

Select cell B1
Goto the menu Data>Filter>AutoFilter
Excel automatically includes column A in the filter.
Filter column B on 1 and note the results.

When you apply the filter you have to make sure Excel doesn't include column
A in the filtered range.

 

[Glenn]

Try this:

Enter this column Header in B1: Name

Enter this data in B2:B6: 1;2;3;1;4

Enter this formula in A2 and copy down to A6:

=SUBTOTAL(3,B$2:B2)

Now, apply the filter...

Select cell B1
Goto the menu Data>Filter>AutoFilter
Excel automatically includes column A in the filter.
Filter column B on 1 and note the results.

When you apply the filter you have to make sure Excel doesn't include column
A in the filtered range.

Interesting...it doesn't appear to recognize the last row as part of the range.
Any idea why?

 

[T. Valko]

Don't know why it does that.