VBA Macro to Only Hide Empty Rows that Had Value in the First Place

You can add the IsEmpty function as a criteria:

Sub DeleteZero()
On Error Resume Next


Dim i As Integer
Dim j As Integer
Dim WS As Integer

Application.ScreenUpdating = False

WS = ActiveWorkbook.Worksheets.Count

For j = 1 To WS
    ActiveWorkbook.Worksheets(j).Activate
    ActiveSheet.Calculate
    If Left(ActiveSheet.Name, 4) <> "OSR_" Then
        For i = 1 To 1000
             If Range("E" & i).Value = 0 And IsEmpty(Range("E" & i).Value) = False And Range("F" & i).Value = 0 And IsEmpty(Range("F" & i).Value) = False And _
             Range("G" & i).Value = 0 And IsEmpty(Range("G" & i).Value) = False And Range("H" & i).Value = 0 And IsEmpty(Range("H" & i).Value) = False Then
                Rows(i).EntireRow.Hidden = True
            End If
        Next i
    End If
Next j

ActiveWorkbook.Worksheets(1).Activate

Application.ScreenUpdating = True

End Sub

Select allOpen in new window

Paul

Try this. Unless you have a reason for it I would delete line 4.

Option Explicit

Sub MySub()
On Error Resume Next


Dim i As Integer
Dim j As Integer
Dim WS As Integer
Dim r As Range

Application.ScreenUpdating = False

WS = ActiveWorkbook.Worksheets.Count

For j = 1 To WS
    ActiveWorkbook.Worksheets(j).Activate
    ActiveSheet.Calculate
    If Left(ActiveSheet.Name, 4) <> "OSR_" Then
        For i = 1 To 1000
            Set r = Sheet.Rows(i)
            If WorksheetFunction.CountA(r) > 0 Then
                If Range("E" & i).Value = 0 And Range("F" & i).Value = 0 And Range("G" & i).Value = 0 And Range("H" & i).Value = 0 Then
                    Rows(i).EntireRow.Hidden = True
                End If
            End If
        Next i
    End If
Next j

ActiveWorkbook.Worksheets(1).Activate

Application.ScreenUpdating = True

End Sub

Select allOpen in new window

Neither of these two macros are adequate.
The first makes no changes.
The second creates the error "Object variable not defined" at Set r = Sheet.Rows(i).

Sorry about that.

Sub MySub()
'On Error Resume Next


Dim i As Integer
Dim j As Integer
Dim WS As Worksheet
Dim r As Range

Application.ScreenUpdating = False


For Each WS In Worksheets
    WS.Activate
    ActiveSheet.Calculate
    If Left(WS.Name, 4) <> "OSR_" Then
        For i = 1 To 1000
            Set r = WS.Rows(i)
            If WorksheetFunction.CountA(r) > 0 Then
                If Range("E" & i).Value = 0 And Range("F" & i).Value = 0 And Range("G" & i).Value = 0 And Range("H" & i).Value = 0 Then
                    Rows(i).EntireRow.Hidden = True
                End If
            End If
        Next i
    End If
Next

ActiveWorkbook.Worksheets(1).Activate

Application.ScreenUpdating = True

End Sub

Select allOpen in new window

No, I got an error as shown in the attached Word doc.
BTW, Columns D, F, H, and J are the columns that I'm using.  That's just an FYI.

Macro.docx

I think you'll find it easier to use this when you want to show an image.

BTW, Columns D, F, H, and J are the columns that I'm using

That's not what your original code showed but the attached workbook contains code that looks for zeros in those columns. Click the 'Hide Rows' button.
29187865.xlsm

Thanks!  Was this designed any differently from your previous macro?

My first submission was untested code.
In my second one I

• Changed your WS object from an Integer to a Worksheet object
• Change your For/Next loop based on j to a For Each/Next loop based on the WS object
• Eliminated the On Error line

In the workbook I changed the columns based on your FYI and added a button.

Does it work now?

Well, it's working. But, it still throws up that error.  It's almost as if the error is more of a nuisance than an error.
Is there any way to get rid of it?

Can you supply a workbook with data that causes the problem? You can change or delete any sensitive data.

No, I really can't.  What are the general remedies for type mismatch errors?

Surely you can copy one or two rows that have the problem to a new workbook and change sensitive data.

I think that I see what the issue is, actually.

I hovered over the yellow line and I see that it is trying to evaluate row 4 of the spreadsheet which contains the phrase "For the Nine Months Ended March 31, 2020".  Obivously, that is a series of Strings and not Integers and, hence, the "Type Mismatch" error.

Can you modify the script to not evaluate the full worksheet, similar to my original script below?

Software Engineer

Sub OSR_ReportComplete()
 '---------------------------------------------------
 'Created By: Seiji Naganuma
 'Created on: June 17, 2013
 'Purpose: Hides rows who's values are 0. Sometimes unchecking "Display Blank Records" doesn't work.
 '---------------------------------------------------
 
On Error Resume Next
 

 Dim i As Integer
 Dim j As Integer
 Dim WS As Integer
 
Application.ScreenUpdating = False
 
WS = ActiveWorkbook.Worksheets.Count
 
For j = 1 To WS
     ActiveWorkbook.Worksheets(j).Activate
     ActiveSheet.Calculate
     If Left(ActiveSheet.Name, 4) <> "OSR_" Then
         For i = 1 To 1000
              If Range("D" & i).Value = 0 And Range("F" & i).Value = 0 And Range("H" & i).Value = 0 And Range("J" & i).Value = 0 Then
                 Rows(i).EntireRow.Hidden = True
             End If
         Next i
     End If
 Next j
 
ActiveWorkbook.Worksheets(1).Activate
 
Application.ScreenUpdating = True
 
End Sub

Select allOpen in new window

That's not my code but try making this change

'If Range("D" & i).Value = 0 And Range("F" & i).Value = 0 And Range("H" & i).Value = 0 And Range("J" & i).Value = 0 Then
If Range("D" & i) = "0" And Range("F" & i) = "0" And Range("H" & i) = "0" And Range("J" & i) = "0" Then

Select allOpen in new window

Yes, that was not your code.  That was my original code.  I was asking for help in modifying it to meet the request of this case.
Anyway, I made the change the you suggested.  It gave no error but no results either.
As I attached earlier in the case, below is a screenshot of one of the tabs of the workbook.  I hope that this helps.
Instead of using the worksheet as a range, could some other range be used?

Capture.PNG

I don't see any zeros in that picture so there's nothing to hide. If you don't want to hide rows with actual zeros in them please describe in another way the rows that you want to hide, lr tell me by row number which of the rows in the picture should be hidden.

I described this in the screenshots at the beginning of this case.  Please refer to that.

There's no possible way for me to produce the "after" picture without access to the real or similar data. Sorry.

Okay, I have attached one of the workbook's tabs and removed the name of the company.  
Hopefully, this will help.  For example, you'll notice the hyphens that represent 0 amounts.
Please let me know if you need anything else.

Book2.xlsx

Yes it does help but the only dashes that I see are in column 'F' so in this case do you want to hide those rows that have the dashes and for now ignore the other columns?

This workbook makes that assumption.
29187865a.xlsm

Hi Martin:
Is this the same macro, as before?
Software Engineer

Essentially yes but with a major change to the line that looks for "zeros"/

Hi Martin:
It's close.  But, there are still a couple of items.
First, as shown in the attached screenshot, there are still records with blanks for dollar amounts. Those records need to "go away".
Also, the macro needs to work for all of the potential tabs of the workbook - not just the one that we were just exchanging.
Thank you, so much, for your efforts thus far!  They're much appreciated!
Software Engineer
Capture.PNG

Please give me a row number or two in the workbook you attached where that is true.

Also, the macro needs to work for all of the potential tabs of the workbook

It will work on every sheet where the worksheet name does not start with "OSR_".

Rows 60, 61, for example.  :)

It's hard for me to tell which rows you want to keep and which you don't. In the 'a' version of this workbook the code hides all the rows where there is a hyphen in column F. You now seem to be saying that you also want to hide all the rows where column F is blank. If I did that then this would be the complete result.
It seems to me that that hides too many rows (row 10 for example) so if I changed the code to ignore rows where column 'A' was blank (like 10) but hide others where column F is blank this this would be the result.
But I don't think that's what you want either, so please help me out with more information about the rows to show and the rows to hide.

Hi Martin:
Thank you, for your excellent support and for staying with this, Martin!
Yeah, the problem is that the original macro works.  But, the end user points out that it is eliminating too many blank rows.
Attached is a screenshot showing a tab from before running the original macro and a screenshot showing the same tab after running the original macro.
The end user says that, based on the "after" results, too many rows now are hidden.
That's the issue.
Software Engineer

Before.PNG
After.png

Please let me know if these are the rules for hiding rows.

• A row should be hidden if column 'F' contains a dash
• A row should be hidden if column 'F' is empty and column 'A' is NOT empty

Hi Martin:
I'm multitasking.  So, my best initial response is "yes".  Could we try something like this, to see if it works?
Software Engineer

Okay this workbook does that. Please remember that the code currently only looks for dashes in column 'F' but that could be easily extended to columns 'D', 'H' and 'J' if the results for column 'F' in this workbook are acceptable.
29187865b.xlsm

Hi Martin:
The results of F appear suitable.  Would you mind please conducting the same for the other columns, so that we may review the results?
Thanks, again!
Software Engineer

I assume then that

• A row should be hidden if column 'D' contains a dash or is empty AND column 'F' contains a dash or is empty AND column 'H' contains a dash or is empty AND column 'J' contains a dash or is empty
• Rows that are blank except for a value in column 'A" will NOT be hidden

Please see the attached workbook which follows those rules. I've documented the code to make it more understandable, and in the documentation I made a suggestion about looping from 1 to 1000 which would speed up the processing a small amount. I also added some test data.
29187865c.xlsm

Attached shows a compile error.
Now, what?

Capture.PNG

I don't get one. Please copy and paste the sub you are using so I can take a look at the code in detail.

I simply copied and pasted the code that you sent from your most recent spreadsheet into mine.  So, my sub is your sub.  :)

I'm at a loss to explain it then but try this.

1. Quit (not just close) Excel
2. Download a fresh copy of the 'c' version of my workbook
3. Start Excel
4. Open the new copy of the workbook
5. Run the sub

If that doesn't work I can write the code in a different way but I'd rather not have to do that.

I should have asked - does it happen in my workbook?

Good Morning, Martin:
Rows 20 and 22 appear to still be showing blanks, according to the screenshot that I just attached.
But, I do not get a "compile error".
Any thoughts?
Software Engineer
Capture.PNG

Rows 20 and 22 appear to still be showing blanks

The code that you posted in your question had these lines.

If Range("E" & i).Value = 0 And Range("F" & i).Value = 0 And Range("G" & i).Value = 0 And Range("H" & i).Value = 0 Then
    Rows(i).EntireRow.Hidden = True
End If

Select allOpen in new window

which said if 'E' has a value of zero and 'F' has a value of zero and 'G' has a value of zero and 'H' has a value of zero then hide the row. Looking for zero didn't work but the "And" told me that you wanted to hide the row only if all those columns had zero. The columns were corrected to 'D', 'F', 'H' and 'J' and the code now looks for a dash or blank in those columns and so Is it true you want to hide the row only if all those columns have a dash or are blank or do you want to hide the row if for example only column 'F' contains a dash or is blank like in rows 20 and 22?

Hi Martin:
Hopefully, this will clear things up.  I apologize for the confusion.
If you look at the first attached screenshot, you will see a blank row after the word "Total".  The macro takes that row away.
Does that make it more clear?
Thanks!
Software Engineer
Before.PNG
After.PNG

Sorry, but no.

Here's is what I see after running the code in the 'c' version of my workbook and it seems to me that the blank rows after the "Total" row are not taken away.

Okay.  Well, back to the other problem.  Any thoughts on the "compile error"?  What are the next steps?

But, I do not get a "compile error".

From this that you posted 10 hours ago, what has changed?

I meant that I did not get a compile error when I ran the macro in your most recent spreadsheet.  :)

OK, so have I given you a satisfactory solution for your original question?

No, last Friday, I got that compile error in my spreadsheet after using the code in the most recent spreadsheet sent.  :)

But then you said that you didn't get one. Compile errors don't come and go unless you change the code. Show me the code you are using by copy and pasting it here.

sigh......i did that last Friday

I really want to help you here but I'm confused about what code you mean. Are you talking about the code you posted here? If so, why, since that code deletes too much including the rows following the Total line. Why aren't you using and referring to the code I wrote?

I did refer to the code that you wrote as of last Friday and got the compile error.
Once again, below is my code.  
Would it be possible for you to please for now ensure that any blank rows after the word "Total" are retained?  The macro takes that row away.
If we can, at least get that piece working and without errors, then we may be "home".
Thank you, so much, Martin!
Software Engineer

Sub OSR_ReportComplete()
 
 
On Error Resume Next
 

 Dim i As Integer
 Dim j As Integer
 Dim WS As Integer
 
Application.ScreenUpdating = False
 
WS = ActiveWorkbook.Worksheets.Count
 
For j = 1 To WS
     ActiveWorkbook.Worksheets(j).Activate
     ActiveSheet.Calculate
     If Left(ActiveSheet.Name, 4) <> "OSR_" Then
         For i = 1 To 1000
              If Range("D" & i).Value = 0 And Range("F" & i).Value = 0 And Range("H" & i).Value = 0 And Range("J" & i).Value = 0 Then
                 Rows(i).EntireRow.Hidden = True
             End If
         Next i
     End If
 Next j
 
ActiveWorkbook.Worksheets(1).Activate
 
Application.ScreenUpdating = True
 
End Sub
 
 

Select allOpen in new window

Thank you, Martin!  It worked!
Software Engineer

You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2019
              Experts Exchange Top Expert VBA 2018, 2019
              Experts Exchange Distinguished Expert in Excel 2018