#1   Report Post  
Mark
 
Posts: n/a
Default 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   Report Post  
Gary L Brown
 
Posts: n/a
Default

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   Report Post  
Mark
 
Posts: n/a
Default

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?

  #4   Report Post  
Gary L Brown
 
Posts: n/a
Default

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?

  #5   Report Post  
Mark
 
Posts: n/a
Default

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   Report Post  
Gary L Brown
 
Posts: n/a
Default

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?

  #7   Report Post  
Mark
 
Posts: n/a
Default

Subtotal was not an option so I typed it in. Result provided is #VALUE!.
Did I tell you this column is a list of names, not numbers? I am not summing
up numbers. I just want to know how many rows there are because some are
hidden and final row, called 2700 by Excel, is too large. I hope I am clear
about this.

"Gary L Brown" wrote:

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?

  #8   Report Post  
Gary L Brown
 
Posts: n/a
Default

Good news/ Bad News...
Good news...
- You definitely have the SubTotal() function because otherwise Excel would
be saying #NAME? instead of #VALUE!
- You simply need to change the formula from a 9 to a 3. ie:
=SubTotal(3,A1:A10) vs =SubTotal(9,A1:A10)

Bad news...
- There are one or more cells in the range you selected to sum/count that
have an error value of #VALUE!
- You are going to have to find and fix them prior to this built-in function
working as you want it to.

FYI,
In HELP you will find that..
9 = sum
3 = counta
1 = average
2 = count
4 = max
etc thru 11 = varp

HTH,
--
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"Mark" wrote:

Subtotal was not an option so I typed it in. Result provided is #VALUE!.
Did I tell you this column is a list of names, not numbers? I am not summing
up numbers. I just want to know how many rows there are because some are
hidden and final row, called 2700 by Excel, is too large. I hope I am clear
about this.

"Gary L Brown" wrote:

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?

  #9   Report Post  
Mark
 
Posts: n/a
Default

Got it! Thanks so much, Gary.
Mark

"Gary L Brown" wrote:

Good news/ Bad News...
Good news...
- You definitely have the SubTotal() function because otherwise Excel would
be saying #NAME? instead of #VALUE!
- You simply need to change the formula from a 9 to a 3. ie:
=SubTotal(3,A1:A10) vs =SubTotal(9,A1:A10)

Bad news...
- There are one or more cells in the range you selected to sum/count that
have an error value of #VALUE!
- You are going to have to find and fix them prior to this built-in function
working as you want it to.

FYI,
In HELP you will find that..
9 = sum
3 = counta
1 = average
2 = count
4 = max
etc thru 11 = varp

HTH,
--
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"Mark" wrote:

Subtotal was not an option so I typed it in. Result provided is #VALUE!.
Did I tell you this column is a list of names, not numbers? I am not summing
up numbers. I just want to know how many rows there are because some are
hidden and final row, called 2700 by Excel, is too large. I hope I am clear
about this.

"Gary L Brown" wrote:

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sequencing a Cell over print pages Benjamin Excel Discussion (Misc queries) 0 February 8th 05 11:43 PM
Formula for Sequencing Totals Jeff T Excel Worksheet Functions 13 January 24th 05 04:07 PM
Alphabet sequencing in a conditional format craftwoodman Excel Discussion (Misc queries) 7 January 3rd 05 02:41 AM


All times are GMT +1. The time now is 12:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"