Numbering after using autofilter

  • Thread starter Thread starter Matt Pennington
  • Start date Start date
M

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?
 
I would copy the autofiltered range to a separate sheet where numbering is
automatically done!

Regards,
Stefi


„Matt Pennington†ezt írta:
 
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 said:
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?
Click to expand...

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 said:
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)
Click to expand...
 
Just make sure you put the formula column *outside* of the filtered range.
Otherwise, you could get incorrect filtered data.
 
T. Valko said:
Just make sure you put the formula column *outside* of the filtered range.
Otherwise, you could get incorrect filtered data.
Click to expand...


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 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.
 
T. Valko said:
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.
Click to expand...


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