software

How do I use SUM or SUBTOTAL ignoring the hidden rows.
I have tried using 109 but it only comes up with VALUE
I am using XP Small Business

If my memory is correct, those 100 series numbers were added in xl2003.

=subtotal() will ignore rows hidden by an autofilter, though.

Carol wrote:
gt;
gt; How do I use SUM or SUBTOTAL ignoring the hidden rows.
gt; I have tried using 109 but it only comes up with VALUE
gt; I am using XP Small Business

--

Dave Peterson

Thanks but we don't autofilter we hide the row(s) in question need a total.

Anybody else any views please???

Carol

quot;Dave Petersonquot; wrote:

gt; If my memory is correct, those 100 series numbers were added in xl2003.
gt;
gt; =subtotal() will ignore rows hidden by an autofilter, though.
gt;
gt; Carol wrote:
gt; gt;
gt; gt; How do I use SUM or SUBTOTAL ignoring the hidden rows.
gt; gt; I have tried using 109 but it only comes up with VALUE
gt; gt; I am using XP Small Business
gt;
gt; --
gt;
gt; Dave Peterson
gt;

You'll need a little user defined function that does the work for you:

Option Explicit
Function SumVisible(rng As Range)

Application.Volatile

Dim myTotal As Double
Dim myCell As Range

myTotal = 0
For Each myCell In rng.Cells
If Application.IsNumber(myCell.Value) Then
If myCell.EntireRow.Hidden = False _
And myCell.EntireColumn.Hidden = False Then
myTotal = myTotal myCell.Value
End If
End If
Next myCell

SumVisible = myTotal

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=sumvisible(a1:a100)

Be aware that some versions of excel won't do a calculation when you hide a
row. So your results could be one calculation behind. Force a new recalc
before you trust that answer.
Carol wrote:
gt;
gt; Thanks but we don't autofilter we hide the row(s) in question need a total.
gt;
gt; Anybody else any views please???
gt;
gt; Carol
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; If my memory is correct, those 100 series numbers were added in xl2003.
gt; gt;
gt; gt; =subtotal() will ignore rows hidden by an autofilter, though.
gt; gt;
gt; gt; Carol wrote:
gt; gt; gt;
gt; gt; gt; How do I use SUM or SUBTOTAL ignoring the hidden rows.
gt; gt; gt; I have tried using 109 but it only comes up with VALUE
gt; gt; gt; I am using XP Small Business
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

Thanks for that but is there an easier way as I don't understand how to
'paste the code in there'
What is the code??

Yes I know I'm dim

quot;Dave Petersonquot; wrote:

gt; You'll need a little user defined function that does the work for you:
gt;
gt; Option Explicit
gt; Function SumVisible(rng As Range)
gt;
gt; Application.Volatile
gt;
gt; Dim myTotal As Double
gt; Dim myCell As Range
gt;
gt; myTotal = 0
gt; For Each myCell In rng.Cells
gt; If Application.IsNumber(myCell.Value) Then
gt; If myCell.EntireRow.Hidden = False _
gt; And myCell.EntireColumn.Hidden = False Then
gt; myTotal = myTotal myCell.Value
gt; End If
gt; End If
gt; Next myCell
gt;
gt; SumVisible = myTotal
gt;
gt; End Function
gt;
gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt;
gt; Short course:
gt;
gt; Open your workbook.
gt; Hit alt-f11 to get to the VBE (where macros/UDF's live)
gt; hit ctrl-R to view the project explorer
gt; Find your workbook.
gt; should look like: VBAProject (yourfilename.xls)
gt;
gt; right click on the project name
gt; Insert, then Module
gt; You should see the code window pop up on the right hand side
gt;
gt; Paste the code in there.
gt;
gt; Now go back to excel.
gt; Into a test cell and type:
gt; =sumvisible(a1:a100)
gt;
gt; Be aware that some versions of excel won't do a calculation when you hide a
gt; row. So your results could be one calculation behind. Force a new recalc
gt; before you trust that answer.
gt;
gt;
gt;
gt; Carol wrote:
gt; gt;
gt; gt; Thanks but we don't autofilter we hide the row(s) in question need a total.
gt; gt;
gt; gt; Anybody else any views please???
gt; gt;
gt; gt; Carol
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; If my memory is correct, those 100 series numbers were added in xl2003.
gt; gt; gt;
gt; gt; gt; =subtotal() will ignore rows hidden by an autofilter, though.
gt; gt; gt;
gt; gt; gt; Carol wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; How do I use SUM or SUBTOTAL ignoring the hidden rows.
gt; gt; gt; gt; I have tried using 109 but it only comes up with VALUE
gt; gt; gt; gt; I am using XP Small Business
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

The code is everything between (and including)

Option Explicit
....
End Sub

And if you read that link or follow those instructions, you may find it not so
difficult.

The only other way I know is to upgrade to xl2003.

Carol wrote:
gt;
gt; Thanks for that but is there an easier way as I don't understand how to
gt; 'paste the code in there'
gt; What is the code??
gt;
gt; Yes I know I'm dim
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; You'll need a little user defined function that does the work for you:
gt; gt;
gt; gt; Option Explicit
gt; gt; Function SumVisible(rng As Range)
gt; gt;
gt; gt; Application.Volatile
gt; gt;
gt; gt; Dim myTotal As Double
gt; gt; Dim myCell As Range
gt; gt;
gt; gt; myTotal = 0
gt; gt; For Each myCell In rng.Cells
gt; gt; If Application.IsNumber(myCell.Value) Then
gt; gt; If myCell.EntireRow.Hidden = False _
gt; gt; And myCell.EntireColumn.Hidden = False Then
gt; gt; myTotal = myTotal myCell.Value
gt; gt; End If
gt; gt; End If
gt; gt; Next myCell
gt; gt;
gt; gt; SumVisible = myTotal
gt; gt;
gt; gt; End Function
gt; gt;
gt; gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt;
gt; gt; Short course:
gt; gt;
gt; gt; Open your workbook.
gt; gt; Hit alt-f11 to get to the VBE (where macros/UDF's live)
gt; gt; hit ctrl-R to view the project explorer
gt; gt; Find your workbook.
gt; gt; should look like: VBAProject (yourfilename.xls)
gt; gt;
gt; gt; right click on the project name
gt; gt; Insert, then Module
gt; gt; You should see the code window pop up on the right hand side
gt; gt;
gt; gt; Paste the code in there.
gt; gt;
gt; gt; Now go back to excel.
gt; gt; Into a test cell and type:
gt; gt; =sumvisible(a1:a100)
gt; gt;
gt; gt; Be aware that some versions of excel won't do a calculation when you hide a
gt; gt; row. So your results could be one calculation behind. Force a new recalc
gt; gt; before you trust that answer.
gt; gt;
gt; gt;
gt; gt;
gt; gt; Carol wrote:
gt; gt; gt;
gt; gt; gt; Thanks but we don't autofilter we hide the row(s) in question need a total.
gt; gt; gt;
gt; gt; gt; Anybody else any views please???
gt; gt; gt;
gt; gt; gt; Carol
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; If my memory is correct, those 100 series numbers were added in xl2003.
gt; gt; gt; gt;
gt; gt; gt; gt; =subtotal() will ignore rows hidden by an autofilter, though.
gt; gt; gt; gt;
gt; gt; gt; gt; Carol wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; How do I use SUM or SUBTOTAL ignoring the hidden rows.
gt; gt; gt; gt; gt; I have tried using 109 but it only comes up with VALUE
gt; gt; gt; gt; gt; I am using XP Small Business
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

Thanks for your help I'll have a try

Carol

quot;Dave Petersonquot; wrote:

gt; The code is everything between (and including)
gt;
gt; Option Explicit
gt; ....
gt; End Sub
gt;
gt; And if you read that link or follow those instructions, you may find it not so
gt; difficult.
gt;
gt; The only other way I know is to upgrade to xl2003.
gt;
gt; Carol wrote:
gt; gt;
gt; gt; Thanks for that but is there an easier way as I don't understand how to
gt; gt; 'paste the code in there'
gt; gt; What is the code??
gt; gt;
gt; gt; Yes I know I'm dim
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; You'll need a little user defined function that does the work for you:
gt; gt; gt;
gt; gt; gt; Option Explicit
gt; gt; gt; Function SumVisible(rng As Range)
gt; gt; gt;
gt; gt; gt; Application.Volatile
gt; gt; gt;
gt; gt; gt; Dim myTotal As Double
gt; gt; gt; Dim myCell As Range
gt; gt; gt;
gt; gt; gt; myTotal = 0
gt; gt; gt; For Each myCell In rng.Cells
gt; gt; gt; If Application.IsNumber(myCell.Value) Then
gt; gt; gt; If myCell.EntireRow.Hidden = False _
gt; gt; gt; And myCell.EntireColumn.Hidden = False Then
gt; gt; gt; myTotal = myTotal myCell.Value
gt; gt; gt; End If
gt; gt; gt; End If
gt; gt; gt; Next myCell
gt; gt; gt;
gt; gt; gt; SumVisible = myTotal
gt; gt; gt;
gt; gt; gt; End Function
gt; gt; gt;
gt; gt; gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt; gt;
gt; gt; gt; Short course:
gt; gt; gt;
gt; gt; gt; Open your workbook.
gt; gt; gt; Hit alt-f11 to get to the VBE (where macros/UDF's live)
gt; gt; gt; hit ctrl-R to view the project explorer
gt; gt; gt; Find your workbook.
gt; gt; gt; should look like: VBAProject (yourfilename.xls)
gt; gt; gt;
gt; gt; gt; right click on the project name
gt; gt; gt; Insert, then Module
gt; gt; gt; You should see the code window pop up on the right hand side
gt; gt; gt;
gt; gt; gt; Paste the code in there.
gt; gt; gt;
gt; gt; gt; Now go back to excel.
gt; gt; gt; Into a test cell and type:
gt; gt; gt; =sumvisible(a1:a100)
gt; gt; gt;
gt; gt; gt; Be aware that some versions of excel won't do a calculation when you hide a
gt; gt; gt; row. So your results could be one calculation behind. Force a new recalc
gt; gt; gt; before you trust that answer.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Carol wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks but we don't autofilter we hide the row(s) in question need a total.
gt; gt; gt; gt;
gt; gt; gt; gt; Anybody else any views please???
gt; gt; gt; gt;
gt; gt; gt; gt; Carol
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; If my memory is correct, those 100 series numbers were added in xl2003.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; =subtotal() will ignore rows hidden by an autofilter, though.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Carol wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; How do I use SUM or SUBTOTAL ignoring the hidden rows.
gt; gt; gt; gt; gt; gt; I have tried using 109 but it only comes up with VALUE
gt; gt; gt; gt; gt; gt; I am using XP Small Business
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

Hi

I've tried exactly as you have said but now i get an answer of £0.00 when I
adda column of figures what am I doing wrong now?

(Yes I am adding the correct colum using the correct cell references)

Thanks

quot;Carolquot; wrote:

gt; Thanks for your help I'll have a try
gt;
gt; Carol
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; The code is everything between (and including)
gt; gt;
gt; gt; Option Explicit
gt; gt; ....
gt; gt; End Sub
gt; gt;
gt; gt; And if you read that link or follow those instructions, you may find it not so
gt; gt; difficult.
gt; gt;
gt; gt; The only other way I know is to upgrade to xl2003.
gt; gt;
gt; gt; Carol wrote:
gt; gt; gt;
gt; gt; gt; Thanks for that but is there an easier way as I don't understand how to
gt; gt; gt; 'paste the code in there'
gt; gt; gt; What is the code??
gt; gt; gt;
gt; gt; gt; Yes I know I'm dim
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; You'll need a little user defined function that does the work for you:
gt; gt; gt; gt;
gt; gt; gt; gt; Option Explicit
gt; gt; gt; gt; Function SumVisible(rng As Range)
gt; gt; gt; gt;
gt; gt; gt; gt; Application.Volatile
gt; gt; gt; gt;
gt; gt; gt; gt; Dim myTotal As Double
gt; gt; gt; gt; Dim myCell As Range
gt; gt; gt; gt;
gt; gt; gt; gt; myTotal = 0
gt; gt; gt; gt; For Each myCell In rng.Cells
gt; gt; gt; gt; If Application.IsNumber(myCell.Value) Then
gt; gt; gt; gt; If myCell.EntireRow.Hidden = False _
gt; gt; gt; gt; And myCell.EntireColumn.Hidden = False Then
gt; gt; gt; gt; myTotal = myTotal myCell.Value
gt; gt; gt; gt; End If
gt; gt; gt; gt; End If
gt; gt; gt; gt; Next myCell
gt; gt; gt; gt;
gt; gt; gt; gt; SumVisible = myTotal
gt; gt; gt; gt;
gt; gt; gt; gt; End Function
gt; gt; gt; gt;
gt; gt; gt; gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; gt; gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt; gt; gt;
gt; gt; gt; gt; Short course:
gt; gt; gt; gt;
gt; gt; gt; gt; Open your workbook.
gt; gt; gt; gt; Hit alt-f11 to get to the VBE (where macros/UDF's live)
gt; gt; gt; gt; hit ctrl-R to view the project explorer
gt; gt; gt; gt; Find your workbook.
gt; gt; gt; gt; should look like: VBAProject (yourfilename.xls)
gt; gt; gt; gt;
gt; gt; gt; gt; right click on the project name
gt; gt; gt; gt; Insert, then Module
gt; gt; gt; gt; You should see the code window pop up on the right hand side
gt; gt; gt; gt;
gt; gt; gt; gt; Paste the code in there.
gt; gt; gt; gt;
gt; gt; gt; gt; Now go back to excel.
gt; gt; gt; gt; Into a test cell and type:
gt; gt; gt; gt; =sumvisible(a1:a100)
gt; gt; gt; gt;
gt; gt; gt; gt; Be aware that some versions of excel won't do a calculation when you hide a
gt; gt; gt; gt; row. So your results could be one calculation behind. Force a new recalc
gt; gt; gt; gt; before you trust that answer.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Carol wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks but we don't autofilter we hide the row(s) in question need a total.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Anybody else any views please???
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Carol
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; If my memory is correct, those 100 series numbers were added in xl2003.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; =subtotal() will ignore rows hidden by an autofilter, though.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Carol wrote:
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; How do I use SUM or SUBTOTAL ignoring the hidden rows.
gt; gt; gt; gt; gt; gt; gt; I have tried using 109 but it only comes up with VALUE
gt; gt; gt; gt; gt; gt; gt; I am using XP Small Business
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

Hi Me Again

I've got the spreadsheet to accept SUMVISIBLE and to show a total now but
it's not correct it adds in the hidden rows as well

What am I doing wrong?

Thanks

quot;Dave Petersonquot; wrote:

gt; The code is everything between (and including)
gt;
gt; Option Explicit
gt; ....
gt; End Sub
gt;
gt; And if you read that link or follow those instructions, you may find it not so
gt; difficult.
gt;
gt; The only other way I know is to upgrade to xl2003.
gt;
gt; Carol wrote:
gt; gt;
gt; gt; Thanks for that but is there an easier way as I don't understand how to
gt; gt; 'paste the code in there'
gt; gt; What is the code??
gt; gt;
gt; gt; Yes I know I'm dim
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; You'll need a little user defined function that does the work for you:
gt; gt; gt;
gt; gt; gt; Option Explicit
gt; gt; gt; Function SumVisible(rng As Range)
gt; gt; gt;
gt; gt; gt; Application.Volatile
gt; gt; gt;
gt; gt; gt; Dim myTotal As Double
gt; gt; gt; Dim myCell As Range
gt; gt; gt;
gt; gt; gt; myTotal = 0
gt; gt; gt; For Each myCell In rng.Cells
gt; gt; gt; If Application.IsNumber(myCell.Value) Then
gt; gt; gt; If myCell.EntireRow.Hidden = False _
gt; gt; gt; And myCell.EntireColumn.Hidden = False Then
gt; gt; gt; myTotal = myTotal myCell.Value
gt; gt; gt; End If
gt; gt; gt; End If
gt; gt; gt; Next myCell
gt; gt; gt;
gt; gt; gt; SumVisible = myTotal
gt; gt; gt;
gt; gt; gt; End Function
gt; gt; gt;
gt; gt; gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt; gt;
gt; gt; gt; Short course:
gt; gt; gt;
gt; gt; gt; Open your workbook.
gt; gt; gt; Hit alt-f11 to get to the VBE (where macros/UDF's live)
gt; gt; gt; hit ctrl-R to view the project explorer
gt; gt; gt; Find your workbook.
gt; gt; gt; should look like: VBAProject (yourfilename.xls)
gt; gt; gt;
gt; gt; gt; right click on the project name
gt; gt; gt; Insert, then Module
gt; gt; gt; You should see the code window pop up on the right hand side
gt; gt; gt;
gt; gt; gt; Paste the code in there.
gt; gt; gt;
gt; gt; gt; Now go back to excel.
gt; gt; gt; Into a test cell and type:
gt; gt; gt; =sumvisible(a1:a100)
gt; gt; gt;
gt; gt; gt; Be aware that some versions of excel won't do a calculation when you hide a
gt; gt; gt; row. So your results could be one calculation behind. Force a new recalc
gt; gt; gt; before you trust that answer.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Carol wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks but we don't autofilter we hide the row(s) in question need a total.
gt; gt; gt; gt;
gt; gt; gt; gt; Anybody else any views please???
gt; gt; gt; gt;
gt; gt; gt; gt; Carol
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; If my memory is correct, those 100 series numbers were added in xl2003.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; =subtotal() will ignore rows hidden by an autofilter, though.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Carol wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; How do I use SUM or SUBTOTAL ignoring the hidden rows.
gt; gt; gt; gt; gt; gt; I have tried using 109 but it only comes up with VALUE
gt; gt; gt; gt; gt; gt; I am using XP Small Business
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

If the formula returns a number, then I think you did everything ok.

But if you've hidden/unhidden any rows/columns, then formula may not have
recalculated.

Try hitting F9 to see if that helps.
Carol wrote:
gt;
gt; Hi Me Again
gt;
gt; I've got the spreadsheet to accept SUMVISIBLE and to show a total now but
gt; it's not correct it adds in the hidden rows as well
gt;
gt; What am I doing wrong?
gt;
gt; Thanks
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; The code is everything between (and including)
gt; gt;
gt; gt; Option Explicit
gt; gt; ....
gt; gt; End Sub
gt; gt;
gt; gt; And if you read that link or follow those instructions, you may find it not so
gt; gt; difficult.
gt; gt;
gt; gt; The only other way I know is to upgrade to xl2003.
gt; gt;
gt; gt; Carol wrote:
gt; gt; gt;
gt; gt; gt; Thanks for that but is there an easier way as I don't understand how to
gt; gt; gt; 'paste the code in there'
gt; gt; gt; What is the code??
gt; gt; gt;
gt; gt; gt; Yes I know I'm dim
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; You'll need a little user defined function that does the work for you:
gt; gt; gt; gt;
gt; gt; gt; gt; Option Explicit
gt; gt; gt; gt; Function SumVisible(rng As Range)
gt; gt; gt; gt;
gt; gt; gt; gt; Application.Volatile
gt; gt; gt; gt;
gt; gt; gt; gt; Dim myTotal As Double
gt; gt; gt; gt; Dim myCell As Range
gt; gt; gt; gt;
gt; gt; gt; gt; myTotal = 0
gt; gt; gt; gt; For Each myCell In rng.Cells
gt; gt; gt; gt; If Application.IsNumber(myCell.Value) Then
gt; gt; gt; gt; If myCell.EntireRow.Hidden = False _
gt; gt; gt; gt; And myCell.EntireColumn.Hidden = False Then
gt; gt; gt; gt; myTotal = myTotal myCell.Value
gt; gt; gt; gt; End If
gt; gt; gt; gt; End If
gt; gt; gt; gt; Next myCell
gt; gt; gt; gt;
gt; gt; gt; gt; SumVisible = myTotal
gt; gt; gt; gt;
gt; gt; gt; gt; End Function
gt; gt; gt; gt;
gt; gt; gt; gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; gt; gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt; gt; gt;
gt; gt; gt; gt; Short course:
gt; gt; gt; gt;
gt; gt; gt; gt; Open your workbook.
gt; gt; gt; gt; Hit alt-f11 to get to the VBE (where macros/UDF's live)
gt; gt; gt; gt; hit ctrl-R to view the project explorer
gt; gt; gt; gt; Find your workbook.
gt; gt; gt; gt; should look like: VBAProject (yourfilename.xls)
gt; gt; gt; gt;
gt; gt; gt; gt; right click on the project name
gt; gt; gt; gt; Insert, then Module
gt; gt; gt; gt; You should see the code window pop up on the right hand side
gt; gt; gt; gt;
gt; gt; gt; gt; Paste the code in there.
gt; gt; gt; gt;
gt; gt; gt; gt; Now go back to excel.
gt; gt; gt; gt; Into a test cell and type:
gt; gt; gt; gt; =sumvisible(a1:a100)
gt; gt; gt; gt;
gt; gt; gt; gt; Be aware that some versions of excel won't do a calculation when you hide a
gt; gt; gt; gt; row. So your results could be one calculation behind. Force a new recalc
gt; gt; gt; gt; before you trust that answer.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Carol wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks but we don't autofilter we hide the row(s) in question need a total.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Anybody else any views please???
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Carol
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; If my memory is correct, those 100 series numbers were added in xl2003.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; =subtotal() will ignore rows hidden by an autofilter, though.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Carol wrote:
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; How do I use SUM or SUBTOTAL ignoring the hidden rows.
gt; gt; gt; gt; gt; gt; gt; I have tried using 109 but it only comes up with VALUE
gt; gt; gt; gt; gt; gt; gt; I am using XP Small Business
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson