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?

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

Regards,
Stefi


€žMatt Pennington€� ezt Ã*rta:

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?

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

"Matt Pennington" wrote:

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?

Matt Pennington wrote:
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:D, put
the following in A2 and copy down as needed:

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

Genius Glenn - this is perfect thanks alot!

"Glenn" wrote:

Matt Pennington wrote:
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:D, put
the following in A2 and copy down as needed:

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

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



"Mike H" wrote:

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

"Matt Pennington" wrote:

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?

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

--
Biff
Microsoft Excel MVP


"Matt Pennington" wrote in
message ...
Genius Glenn - this is perfect thanks alot!

"Glenn" wrote:

Matt Pennington wrote:
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:D, put
the following in A2 and copy down as needed:

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

T. Valko wrote:
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...

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 DataFilterAutoFilter
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.

--
Biff
Microsoft Excel MVP


"Glenn" wrote in message
...
T. Valko wrote:
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 wrote:
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 DataFilterAutoFilter
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?

Don't know why it does that.

--
Biff
Microsoft Excel MVP


"Glenn" wrote in message
...
T. Valko wrote:
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 DataFilterAutoFilter
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?