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?
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
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