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

0 votes

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?

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

Feb 13, 2023 in Others by Kithuzzz
• 38,010 points
379 views

1 answer to this question.

0 votes

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
answered Feb 13, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

How to find out if an item is present in an std::vector?

The most straightforward solution is to count the total number of elements in the vector that have the specified value.  If the count is greater than zero, we've found our element.  This is simple to accomplish with the std::count function. #include <iostream> #include <vector> #include <algorithm> int main() { ...READ MORE

answered May 27, 2022 in Others by Damon
• 4,960 points
10,295 views
0 votes
1 answer

How to remove Blank Rows using EPPlus Excel Package Plus

Check this answer here: https://stackoverflow.com/a/49232456/1114531.  It is checking the ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
2,352 views
0 votes
1 answer

Is there any way in python to auto-correct spelling mistake in multiple rows of an excel files of a single column?

Use Spellchecker for doing your stuff: import pandas ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,420 points
1,469 views
0 votes
1 answer

How to freeze an area in Excel?

Excel has a "Freeze panes" for this, ...READ MORE

answered Oct 17, 2022 in Others by narikkadan
• 63,420 points
400 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,420 points
930 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
829 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,126 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
421 views
0 votes
1 answer

How to trick an Excel function that wants a column as input to accept a list of values as if these were in a column

Use VSTACK: vstack to make an array: Use it as value ...READ MORE

answered Mar 18, 2023 in Others by narikkadan
• 63,420 points
242 views
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 63,420 points
773 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP