Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbering after using autofilter
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbering after using autofilter
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbering after using autofilter
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbering after using autofilter
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) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbering after using autofilter
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) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbering after using autofilter
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbering after using autofilter
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) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbering after using autofilter
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... |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbering after using autofilter
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... |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbering after using autofilter
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? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbering after using autofilter
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2007 Autofilter worse than 2003 Autofilter | Excel Discussion (Misc queries) | |||
Row Numbering | Excel Discussion (Misc queries) | |||
PO with sequential numbering with start / end numbering | Excel Discussion (Misc queries) | |||
Numbering a Range Using Autofilter | Excel Discussion (Misc queries) | |||
How to Sort within AutoFilter with Protection on (and AutoFilter . | Excel Discussion (Misc queries) |