Hi all. I would appreciate the help on this... When using autofilter, I need to be able to renumber the rows... Column A counts the number of accounts in the whole roster. When I filter for a team member, I need it to restart the numbering from 1 on down, not to display the original numbers. Thanks in advance.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
After Using AutoFilter, Need to Renumber Rows
- Thread starter shqiptar
- Start date
Oorang
Well-known Member
- Joined
- Mar 4, 2005
- Messages
- 2,071
I do not know of a way to do this with formulas. Perhaps a guru could weigh in on that one. However here is a UDF you could use in the mean time:
To use:
Code:
Option Explicit
Function AUTONUMBER(ByVal oRng As Excel.Range, Optional bCountFromZero As Boolean = True) As Long
Excel.Application.Volatile True
On Error GoTo Err_Hnd_AN
Dim oRngCol As Excel.Range
Dim oWS As Excel.Worksheet
If oRng.Cells.Count <> 1 Then VBA.Err.Raise vbObjectError + 777
Set oWS = oRng.Parent
'Used range gets rid of blanks in the upper portion of column, oWS.Rows("1:" & oRng.Row) limits
'the range to the cell in question up.
Set oRngCol = Excel.Intersect(oWS.Columns(oRng.Column), oWS.UsedRange, oWS.Rows("1:" & oRng.Row))
AUTONUMBER = oRng.Row - GetInvisibleRows(oRngCol)
If bCountFromZero Then AUTONUMBER = AUTONUMBER - 1
Exit Function
Err_Hnd_AN:
End Function
Private Function GetInvisibleRows(oRng As Excel.Range) As Long
'SpecialCells(xlCellTypeVisible).Count does not work when
'called from macro.
Dim oWS As Excel.Worksheet
Dim lLC As Long 'Loop Counter
Dim lUB As Long 'Loop Upper bound
Dim lSum As Long
Set oWS = oRng.Parent
lUB = oRng.Rows.Count
For lLC = 1 To lUB
If oWS.Rows(lLC).RowHeight = 0 Then lSum = lSum + 1
Next lLC
GetInvisibleRows = lSum
End Function- Press Alt F11 to Launch VBE.
On Insert Menu select "Insert Module".
In Module paste code.
Put this formula in Cell A2:Copy Forumla down as needed.Code:=AUTONUMBER(A2)
Upvote
0
Similar threads
