excelvbaexcel-2013

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


I have a daily report form that I email at the end of every shift. Some sections of the report take up many rows if I'm working one shift versus another.

If I'm working first shift the staff breaks section takes up close to 10 rows. If I'm working third shift it only takes up three rows. I end up with multiple blank rows.

My manager told me to remove those rows when I email the report.

I currently use VBA code to select a range to Copy as Picture, to send in the email without the formatting being obliterated by Outlook.

How can I 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?

Form Example

Form Example Showing Formulas

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

Solution

  • 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.CountBlank(rng.rows(i)) = _ 
                                         rng.Columns.count 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