Getting compile error on WorksheetFuntion.Sum line
 

I am trying to test if the sum of column 6 in a named range is 0 and if it
is I hide the whole range, if its not I hide only teh lines that are 0 or
blank. I get a complie error on the If WorksheetFuntion.Sum line. What is
the correct syntax?


Application.Goto reference:="MELLandLabor"
Set rng = Range("MELLandLabor")
If WorksheetFunction.Sum(rng.Cells(1, 6), rng.cells(rng.Rows.Count, 6) =
0 then
Selection.EntireRow.Hidden = True
Else
For n = 1 To rng.Rows.Count
rng.Cells(n, 6).Select
If rng.Cells(n, 6) = "" Or rng.Cells(n, 6) = 0 Then
rng.Cells(n, 6).EntireRow.Hidden = True
End If
Next n
End If

Thanks for your Help.

Rick

Getting compile error on WorksheetFuntion.Sum line
 

1. The number of right facing parentheses must equal the number of left facing parentheses.
2. Your sum will equal the total of the individual cells listed (the first and last)...
instead, you should use a Range inside the sum brackets.
--
Jim Cone
Portland, Oregon USA



"froggygremblin"
wrote in message ...
I am trying to test if the sum of column 6 in a named range is 0 and if it
is I hide the whole range, if its not I hide only teh lines that are 0 or
blank. I get a complie error on the If WorksheetFuntion.Sum line. What is
the correct syntax?


Application.Goto reference:="MELLandLabor"
Set rng = Range("MELLandLabor")
If WorksheetFunction.Sum(rng.Cells(1, 6), rng.cells(rng.Rows.Count, 6) =
0 then
Selection.EntireRow.Hidden = True
Else
For n = 1 To rng.Rows.Count
rng.Cells(n, 6).Select
If rng.Cells(n, 6) = "" Or rng.Cells(n, 6) = 0 Then
rng.Cells(n, 6).EntireRow.Hidden = True
End If
Next n
End If

Thanks for your Help.

Rick

Getting compile error on WorksheetFuntion.Sum line
 

Hi FG,

I think you need another ) after 6

Syntax is normally application.worksheetfunction.sum

HTH

"froggygremblin" wrote in message
...
I am trying to test if the sum of column 6 in a named range is 0 and if
it
is I hide the whole range, if its not I hide only teh lines that are 0 or
blank. I get a complie error on the If WorksheetFuntion.Sum line. What
is
the correct syntax?


Application.Goto reference:="MELLandLabor"
Set rng = Range("MELLandLabor")
If WorksheetFunction.Sum(rng.Cells(1, 6), rng.cells(rng.Rows.Count, 6)
=
0 then
Selection.EntireRow.Hidden = True
Else
For n = 1 To rng.Rows.Count
rng.Cells(n, 6).Select
If rng.Cells(n, 6) = "" Or rng.Cells(n, 6) = 0 Then
rng.Cells(n, 6).EntireRow.Hidden = True
End If
Next n
End If

Thanks for your Help.

Rick

Getting compile error on WorksheetFuntion.Sum line
 

Try it this way:
And perhaps this page will give you a little more informationi on why I did
it this way: http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
Also, when using range references in this fashion, you don't have to .Select
them before using them (makes it faster and with reduced screen flickering).

Sub Testing()
Dim n As Integer
Dim rng As Range
Dim testRange As Range

'Application.Goto reference:="MELLandLabor"
Set rng = Range("MELLandLabor")
Set testRange = Range(rng.Cells(1, 6), rng.Cells(rng.Rows.Count, 6))

Application.ScreenUpdating=False ' speed things up
rng.EntireRow.Hidden = False ' make sure all start unhidden
MsgBox Application.WorksheetFunction.Min(testRange)
Stop
MsgBox Application.WorksheetFunction.Sum(testRange)
If Application.WorksheetFunction.Sum(testRange) = 0 Then
rng.EntireRow.Hidden = True
Else
For n = 1 To rng.Rows.Count
'rng.Cells(n, 6).Select
If rng.Cells(n, 6) = "" Or rng.Cells(n, 6) = 0 Then
rng.Cells(n, 6).EntireRow.Hidden = True
End If
Next n
End If

End Sub



"froggygremblin" wrote:

I am trying to test if the sum of column 6 in a named range is 0 and if it
is I hide the whole range, if its not I hide only teh lines that are 0 or
blank. I get a complie error on the If WorksheetFuntion.Sum line. What is
the correct syntax?


Application.Goto reference:="MELLandLabor"
Set rng = Range("MELLandLabor")
If WorksheetFunction.Sum(rng.Cells(1, 6), rng.cells(rng.Rows.Count, 6) =
0 then
Selection.EntireRow.Hidden = True
Else
For n = 1 To rng.Rows.Count
rng.Cells(n, 6).Select
If rng.Cells(n, 6) = "" Or rng.Cells(n, 6) = 0 Then
rng.Cells(n, 6).EntireRow.Hidden = True
End If
Next n
End If

Thanks for your Help.

Rick

Getting compile error on WorksheetFuntion.Sum line
 

Try it this way without selections from anywhere in the workbook.

Sub HideRowsSAS()
Set rng = Range("MELLandLabor")
If Application.Sum(rng.Columns(6)) = 0 Then
rng.Rows.Hidden = True
Else
For n = 1 To rng.Rows.Count
If rng.Cells(n, 6) = "" Or rng.Cells(n, 6) = 0 _
Then rng.Rows(n).Hidden = True
Next n
End If
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"froggygremblin" wrote in message
...
I am trying to test if the sum of column 6 in a named range is 0 and if it
is I hide the whole range, if its not I hide only teh lines that are 0 or
blank. I get a complie error on the If WorksheetFuntion.Sum line. What
is
the correct syntax?


Application.Goto reference:="MELLandLabor"
Set rng = Range("MELLandLabor")
If WorksheetFunction.Sum(rng.Cells(1, 6), rng.cells(rng.Rows.Count, 6)
=
0 then
Selection.EntireRow.Hidden = True
Else
For n = 1 To rng.Rows.Count
rng.Cells(n, 6).Select
If rng.Cells(n, 6) = "" Or rng.Cells(n, 6) = 0 Then
rng.Cells(n, 6).EntireRow.Hidden = True
End If
Next n
End If

Thanks for your Help.

Rick

Getting compile error on WorksheetFuntion.Sum line
 

Thanks for everyones suggestions. I've got it woeking now.

"froggygremblin" wrote:

I am trying to test if the sum of column 6 in a named range is 0 and if it
is I hide the whole range, if its not I hide only teh lines that are 0 or
blank. I get a complie error on the If WorksheetFuntion.Sum line. What is
the correct syntax?


Application.Goto reference:="MELLandLabor"
Set rng = Range("MELLandLabor")
If WorksheetFunction.Sum(rng.Cells(1, 6), rng.cells(rng.Rows.Count, 6) =
0 then
Selection.EntireRow.Hidden = True
Else
For n = 1 To rng.Rows.Count
rng.Cells(n, 6).Select
If rng.Cells(n, 6) = "" Or rng.Cells(n, 6) = 0 Then
rng.Cells(n, 6).EntireRow.Hidden = True
End If
Next n
End If

Thanks for your Help.

Rick

Getting compile error on WorksheetFuntion.Sum line
 

The archives want to see your final solution

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"froggygremblin" wrote in message
...
Thanks for everyones suggestions. I've got it woeking now.

"froggygremblin" wrote:

I am trying to test if the sum of column 6 in a named range is 0 and if
it
is I hide the whole range, if its not I hide only teh lines that are 0 or
blank. I get a complie error on the If WorksheetFuntion.Sum line. What
is
the correct syntax?


Application.Goto reference:="MELLandLabor"
Set rng = Range("MELLandLabor")
If WorksheetFunction.Sum(rng.Cells(1, 6), rng.cells(rng.Rows.Count,
6) =
0 then
Selection.EntireRow.Hidden = True
Else
For n = 1 To rng.Rows.Count
rng.Cells(n, 6).Select
If rng.Cells(n, 6) = "" Or rng.Cells(n, 6) = 0 Then
rng.Cells(n, 6).EntireRow.Hidden = True
End If
Next n
End If

Thanks for your Help.

Rick