Error 13 Type Mismatch

[Justin]

This code is to hide the entire row if the cell in column D is empty.

This code returns the Type MisMatch error:

Sub hideme()
Application.ScreenUpdating = False
Dim myrange As Range, myrange1 As Range
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
Set myrange = Range("D4" & lastrow)
For Each c In myrange
If c.Value = "" Then
If myrange1 Is Nothing Then
Set myrange1 = c.EntireRow
Else
Set myrange1 = Union(myrange1, c.EntireRow)
End If
End If
Next
myrange1.EntireRow.Hidden = True
End Sub

It also does work in my 2003 version but doesn't in the 2007... anyone know
why this is?

 

[Paul]

Is this a case of substituting :

If c.Value = "" Then

for

If IsEmpty(c.Value) Then

 

[Ryan H]

Justin,

I tweaked your code a little and the code below worked just fine for me in
2007. I would also recommend making sure you set the ScreenUpdating property
back to True at the end of your code.

Option Explicit

Sub HideRows()

Dim myRange As Range
Dim myRange1 As Range
Dim LastRow As Long
Dim c As Range

Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, "D").End(xlUp).Row
Set myRange = Range("D4" & LastRow)

For Each c In myRange
If Trim(c.Value) = "" Then
If myRange1 Is Nothing Then
Set myRange1 = c.EntireRow
Else
Set myRange1 = Union(myRange1, c.EntireRow)
End If
End If
Next c

myRange1.EntireRow.Hidden = True

Application.ScreenUpdating = True

End Sub

Hope this helps! If so, click "YES" below.

 

[Justin]

Thanks for replying.... So it gives me the same error run I ran what you had,
however, I created a new workbook places some random values into column D
then ran the macro and everything worked fine. So im not sure what is going
on with it. Can you think of anything esle?

Thanks

Justin

 

[Dave Peterson]

If you have errors (like #n/a or #div/0!) in your cells, you'll get this error.

Maybe...

For Each c In myRange
if iserror(c.value) then
'skip it???
else
If Trim(c.Value) = "" Then
If myRange1 Is Nothing Then
Set myRange1 = c.EntireRow
Else
Set myRange1 = Union(myRange1, c.EntireRow)
End If
End If
end if
Next c

If this doesn't help, what line causes the error?

Maybe adding a line to show the address would help:

For Each c In myRange
msgbox c.address(0,0)
If Trim(c.Value) = "" Then
If myRange1 Is Nothing Then
Set myRange1 = c.EntireRow
Else
Set myRange1 = Union(myRange1, c.EntireRow)
End If
End If
Next c