How to hide blank rows in an excel form if they are blank

Question

At the conclusion of each shift, I email a daily report form. If I'm working one shift instead of another, some elements of the report take up numerous rows. (The staff breaks section occupies about 10 rows when I work first shift, but only three rows when I work third shift.) Therefore, there is no issue when I work the first shift, but when I work the third shift, I have a lot of blank rows.

I didn't think that was a problem, but recently my boss advised me to take those rows out of the report when I email it in at the end of the shift. I currently use a VBA to select a range to Copy as Picture, to send in the email without the formatting being obliterated by Outlook.

Is there some sort of modifier I can add to my code to hide the blank rows before the Copy as Picture takes place so I don't have to search for every blank row and hide them manually?

Also, would the VBA have trouble if there is invisible code already in the cells?

I tried this code hoping it would only hide rows without data, but it hid the entire selection

    Sub Hide_Rows()
    '
    ' Hide_Rows Macro
    '

    '
        Sheet1.Select
        Range("A1:H59").Select
    
        Dim rng As Range
    
        For Each rng In Selection
    
            If rng.Value = "" Then
        
                rng.EntireRow.Hidden = True
            
            End If
        
        Next rng
    
End Sub

Answer 1

If an empty row should be defined as one not having any value in A:A, you can use this compact way:

Sub hideUnhideRowsAtOnce()
   Dim rngHid As Range
   
   Set rngHid = Range("A1:A59").SpecialCells(xlCellTypeBlanks)
   rngHid.EntireRow.Hidden = True
   ' do what you have to do
   '
   Stop 'just to see the result. Press F5 to continue
   rngHid.EntireRow.Hidden = False
End Sub

If some values may exist on the other columns, please try using the next way:

Sub hideUnhideRows()
   Dim rng As Range, rngHid As Range, i As Long
   
   Set rng = Range("A1:H59")

   For i = 1 To rng.rows.count
        If WorksheetFunction.CountA(rng.rows(i)) = 0 Then
            addToRange rngHid, Range("A" & i)
        End If
   Next i
   Debug.Print rngHid.address: Stop
   If Not rngHid Is Nothing Then rngHid.EntireRow.Hidden = True
   
   ' do what you have to do
   '
   Stop 'just to see the result. Press F5 to continue
   
    If Not rngHid Is Nothing Then rngHid.EntireRow.Hidden = False
End Sub

Private Sub addToRange(rngU As Range, rng As Range)
    If rngU Is Nothing Then
        Set rngU = rng
    Else
        Set rngU = Union(rngU, rng)
    End If
End Sub