Hiding blank rows on a different sheet

[yzer19]

I'm having some problems getting this code to work. The code is located in a sheet called "Setup" but I want it to run on a sheet called "New"

Dim rng As Range
  Range("D9:D44").EntireRow.Hidden = False
  For Each ce In Range("D9:D44")
    If IsEmpty(ce) Then
      If rng Is Nothing Then
        Set rng = ce
      Else
        Set rng = Union(rng, ce)
      End If
    End If
  Next ce
  rng.EntireRow.Hidden = True

I have tried modifying the code to the following

Dim rng As Range
  Worksheet("New").Range("D9:D44").EntireRow.Hidden = False
  For Each ce In Worksheet("New").Range("D9:D44")
    If IsEmpty(ce) Then
      If rng Is Nothing Then
        Set rng = ce
      Else
        Set rng = Union(rng, ce)
      End If
    End If
  Next ce
  rng.EntireRow.Hidden = True

When I do this it doesn't like the last row of code. Anybody have any ideas.

[mikerickson]

I needed to change the property from Worksheet to Worksheets.

I got an error on that line when all cells in the range were filled. The IsEmpty always fails and rng never gets Set.
Testing whether rng Is Nothing should fix that.

Dim rng As Range
Dim ce As Range
  Worksheets("New").Range("D9:D44").EntireRow.Hidden = False
  For Each ce In Worksheets("New").Range("D9:D44")
    If IsEmpty(ce) Then
      If rng Is Nothing Then
        Set rng = ce
      Else
        Set rng = Union(rng, ce)
      End If
    End If
  Next ce
  
  If Not rng Is Nothing Then
    rng.EntireRow.Hidden = True
  End If

This also could be done using SpecialCells. The Error code is there to handle the same situation of no blank cells.

Sub test2()
With Worksheets("New").Range("D9:d44")
    .EntireRow.Hidden = False
    On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
    On Error GoTo 0
End With

End Sub

[yzer19]

Thanks Mike the first bit of code worked great. My next problem is the "New" sheet contains a formula in the range d9:d44. The code thinks there is data in these fields and thus won't hide the lines. The formula is a simple one (=Setup!B20) but enough to cause it grief. I need to keep the formula. The output in range d9:d44 is not numbers but words. Can we tweak the code so that it looks for a value in the cells.
Thanks
Rob