| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Row Sequencing
I have done a number of sorts and filters and rows now skip the numbers that
are hidden. Now I am satisfied with the rows showing and I want a straight numerical sequence of the rows shown, without any numbers skipped. Can I get this? |
|
#2
|
|||
|
|||
|
Try this macro - select the range you want to put the numbers first and put
your starting number in the first cell of the selection. '/=================================================/ Sub Row_List() Dim rngCell As Range Dim strAddress As String strAddress = Selection.Range("A1").Address For Each rngCell In Selection If Hidden_Row(rngCell) = False Then If strAddress < rngCell.Address Then rngCell.Formula = "=" & strAddress & " + 1" strAddress = rngCell.Address End If End If Next rngCell End Sub '/=================================================/ Public Function Hidden_Row(rng As Range) As Long 'return 1 if row is hidden, 0 if row is visible Application.Volatile On Error Resume Next Hidden_Row = 0 If rng.EntireRow.Hidden = True Then Hidden_Row = 1 End If End Function '/=================================================/ HTH, -- Gary Brown If this post was helpful, please click the ''''Yes'''' button next to ''''Was this Post Helpfull to you?". "Mark" wrote: I have done a number of sorts and filters and rows now skip the numbers that are hidden. Now I am satisfied with the rows showing and I want a straight numerical sequence of the rows shown, without any numbers skipped. Can I get this? |
|
#3
|
|||
|
|||
|
All I really want is the sum of all rows, since the number of the last row
reflects skipped rows. For example, last row says 2400, but it's actually less since there are hidden rows. I want the actually number of rows showing. Is there a simple way to get this? "Gary L Brown" wrote: Try this macro - select the range you want to put the numbers first and put your starting number in the first cell of the selection. '/=================================================/ Sub Row_List() Dim rngCell As Range Dim strAddress As String strAddress = Selection.Range("A1").Address For Each rngCell In Selection If Hidden_Row(rngCell) = False Then If strAddress < rngCell.Address Then rngCell.Formula = "=" & strAddress & " + 1" strAddress = rngCell.Address End If End If Next rngCell End Sub '/=================================================/ Public Function Hidden_Row(rng As Range) As Long 'return 1 if row is hidden, 0 if row is visible Application.Volatile On Error Resume Next Hidden_Row = 0 If rng.EntireRow.Hidden = True Then Hidden_Row = 1 End If End Function '/=================================================/ HTH, -- Gary Brown If this post was helpful, please click the ''''Yes'''' button next to ''''Was this Post Helpfull to you?". "Mark" wrote: I have done a number of sorts and filters and rows now skip the numbers that are hidden. Now I am satisfied with the rows showing and I want a straight numerical sequence of the rows shown, without any numbers skipped. Can I get this? |
|
#5
|
|||
|
|||
|
I'm afraid I am a rank amateur at this. I have Office 2002 and skipped rows
are indeed due to use of Auto Filter, Advanced Filter, along with some manually deleted rows. Sounds like your Subtotal function might do it. But how exactly to I perform this? When I attempt to highlight rows in far upper left, entire spreadsheet is highlighted. Also in function dialogue box, I don't see Subtotal function. "Gary L Brown" wrote: Hi Mark, There are 3 possible answers but it depends on how you are hiding the rows. 1) If you are using Autofilter then the SubTotal() function will work. =SubTotal(9,A1:A10) will sum only visible rows in the range A1 to A10 =SubTotal(3,A1:A10) will count only visible rows in the range A1 to A10 2) If you are MANUALLY hidding the rows AND you have Excel 2003, you can use the new feature of the SubTotal() function... =SubTotal(109,A1:A10) will sum only visible rows in the range A1 to A10 3) If you are MANUALLY hidding the rows AND you DO NOT have Excel 2003, you need a macro to do this such as the one below. '/=============================================/ Public Function Sum_Visible_Range(rng As Range) As Variant 'sum numbers in visible rows and columns only Dim rngCell As Range Dim varSum As Variant Application.Volatile varSum = 0 For Each rngCell In rng If IsNumeric(rngCell.Value) = True Or _ IsDate(rngCell.Value) Then If rngCell.EntireRow.Hidden = False And _ rngCell.EntireColumn.Hidden = False Then varSum = varSum + rngCell.Value End If End If Next rngCell Sum_Visible_Range = varSum End Function '/=============================================/ HTH, -- Gary Brown If this post was helpful, please click the ''''Yes'''' button next to ''''Was this Post Helpfull to you?". "Mark" wrote: All I really want is the sum of all rows, since the number of the last row reflects skipped rows. For example, last row says 2400, but it's actually less since there are hidden rows. I want the actually number of rows showing. Is there a simple way to get this? "Gary L Brown" wrote: Try this macro - select the range you want to put the numbers first and put your starting number in the first cell of the selection. '/=================================================/ Sub Row_List() Dim rngCell As Range Dim strAddress As String strAddress = Selection.Range("A1").Address For Each rngCell In Selection If Hidden_Row(rngCell) = False Then If strAddress < rngCell.Address Then rngCell.Formula = "=" & strAddress & " + 1" strAddress = rngCell.Address End If End If Next rngCell End Sub '/=================================================/ Public Function Hidden_Row(rng As Range) As Long 'return 1 if row is hidden, 0 if row is visible Application.Volatile On Error Resume Next Hidden_Row = 0 If rng.EntireRow.Hidden = True Then Hidden_Row = 1 End If End Function '/=================================================/ HTH, -- Gary Brown If this post was helpful, please click the ''''Yes'''' button next to ''''Was this Post Helpfull to you?". "Mark" wrote: I have done a number of sorts and filters and rows now skip the numbers that are hidden. Now I am satisfied with the rows showing and I want a straight numerical sequence of the rows shown, without any numbers skipped. Can I get this? |
|
#6
|
|||
|
|||
|
Let's make some assumptions for this example.
You have Autofiltered information in Cells A2 thru A10. You want your Sum to be in Cell A11 - Go to cell A11 - From the top menu, select InsertFunction - In the 'Function Name:' window, scroll down to 'SUBTOTAL' - Highlight 'SUBTOTAL' - Select OK - In the function window that appears.. - In the first window 'Function_num', type 9 - Go to the Ref1 box so that you can select a range - You may have to move the function window if it is in the way of the range you want to select - Hightlight the cells that you want to Sum - Select OK HTH, -- Gary Brown If this post was helpful, please click the ''''Yes'''' button next to ''''Was this Post Helpfull to you?". "Mark" wrote: I'm afraid I am a rank amateur at this. I have Office 2002 and skipped rows are indeed due to use of Auto Filter, Advanced Filter, along with some manually deleted rows. Sounds like your Subtotal function might do it. But how exactly to I perform this? When I attempt to highlight rows in far upper left, entire spreadsheet is highlighted. Also in function dialogue box, I don't see Subtotal function. "Gary L Brown" wrote: Hi Mark, There are 3 possible answers but it depends on how you are hiding the rows. 1) If you are using Autofilter then the SubTotal() function will work. =SubTotal(9,A1:A10) will sum only visible rows in the range A1 to A10 =SubTotal(3,A1:A10) will count only visible rows in the range A1 to A10 2) If you are MANUALLY hidding the rows AND you have Excel 2003, you can use the new feature of the SubTotal() function... =SubTotal(109,A1:A10) will sum only visible rows in the range A1 to A10 3) If you are MANUALLY hidding the rows AND you DO NOT have Excel 2003, you need a macro to do this such as the one below. '/=============================================/ Public Function Sum_Visible_Range(rng As Range) As Variant 'sum numbers in visible rows and columns only Dim rngCell As Range Dim varSum As Variant Application.Volatile varSum = 0 For Each rngCell In rng If IsNumeric(rngCell.Value) = True Or _ IsDate(rngCell.Value) Then If rngCell.EntireRow.Hidden = False And _ rngCell.EntireColumn.Hidden = False Then varSum = varSum + rngCell.Value End If End If Next rngCell Sum_Visible_Range = varSum End Function '/=============================================/ HTH, -- Gary Brown If this post was helpful, please click the ''''Yes'''' button next to ''''Was this Post Helpfull to you?". "Mark" wrote: All I really want is the sum of all rows, since the number of the last row reflects skipped rows. For example, last row says 2400, but it's actually less since there are hidden rows. I want the actually number of rows showing. Is there a simple way to get this? "Gary L Brown" wrote: Try this macro - select the range you want to put the numbers first and put your starting number in the first cell of the selection. '/=================================================/ Sub Row_List() Dim rngCell As Range Dim strAddress As String strAddress = Selection.Range("A1").Address For Each rngCell In Selection If Hidden_Row(rngCell) = False Then If strAddress < rngCell.Address Then rngCell.Formula = "=" & strAddress & " + 1" strAddress = rngCell.Address End If End If Next rngCell End Sub '/=================================================/ Public Function Hidden_Row(rng As Range) As Long 'return 1 if row is hidden, 0 if row is visible Application.Volatile On Error Resume Next Hidden_Row = 0 If rng.EntireRow.Hidden = True Then Hidden_Row = 1 End If End Function '/=================================================/ HTH, -- Gary Brown If this post was helpful, please click the ''''Yes'''' button next to ''''Was this Post Helpfull to you?". "Mark" wrote: I have done a number of sorts and filters and rows now skip the numbers that are hidden. Now I am satisfied with the rows showing and I want a straight numerical sequence of the rows shown, without any numbers skipped. Can I get this? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sequencing a Cell over print pages | Excel Discussion (Misc queries) | |||
| Formula for Sequencing Totals | Excel Worksheet Functions | |||
| Alphabet sequencing in a conditional format | Excel Discussion (Misc queries) | |||
Hybrid Mode
