Getting compile error on WorksheetFuntion.Sum line

[froggygremblin]

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

 

[Jim Cone]

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" <[email protected]>
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

 

[JLatham]

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/library/aa221602(office.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

 

[Don Guillett]

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

 

[froggygremblin]

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

 

[Don Guillett]

The archives want to see your final solution