Run-time error 13 type mismatch Excel 2010 Spreadsheet

mmmcdee
mmmcdee used Ask the Experts™
on
I am getting an error "Run-time error 13 type mismatch" when I run the code below.  The code hide colums if values do not match in a drop down box.  The code runs correctly when I hit END. Although the error continues.

Highlightlighted Error:  
col.EntireColumn.Hidden = _
Application.Sum(col) = 0


----------------------------------------------------------------------------

Sub sheet1hide_empty_columns()

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("d1")) Is Nothing Then
   Application.ScreenUpdating = False

   Dim col As Range
   For Each col In ActiveSheet.Range("C2:aip2").Columns
   col.EntireColumn.Hidden = _
   Application.Sum(col) = 0

   Next
   Application.ScreenUpdating = True
End If
End Sub

--------------------------------------------
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Your code works fine for me (Excel 2007)
Commented:
I suggest you add the following code
Debug.Print col.Column

before the offending line.
I would then check this column manually to make sure that there is nothing in the column that is causing the error. E.g. an #DIV/0 or something like that.

Author

Commented:
In this VERY LARGE spreadsheet - I found the errors were related to 2 formulas that were not calculating correctly.  Thanks for the quick response!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial