We help IT Professionals succeed at work.

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

94 Views
Last Modified: 2020-07-14
Hi:

Below is a macro that we use in Excel.  Its purpose is to hide rows whose dollar amounts are $0.00.

Even though it is conducting that task very well, it is also hiding all rows that were blank to begin with - not just rows having $0.00 amounts.

How do I modify this syntax to still display rows that were completely empty in the first place?

If it helps, I just added "Before" and "After" screenshots showing the issue more prominently.

Thanks!

Software Engineer


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 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
        Next i
    End If
Next j

ActiveWorkbook.Worksheets(1).Activate

Application.ScreenUpdating = True

End Sub

Open in new window


BeforeAfter
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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
Paul
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Open in new window

Author

Commented:
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).
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Open in new window

Author

Commented:
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
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I think you'll find it easier to use this when you want to show an image.
2020-07-09_09-40-52.png
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

Author

Commented:
Thanks!  Was this designed any differently from your previous macro?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:
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?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:
No, I really can't.  What are the general remedies for type mismatch errors?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:
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
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Open in new window

Author

Commented:
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
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

Commented:
I described this in the screenshots at the beginning of this case.  Please refer to that.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:
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
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
This workbook makes that assumption.
29187865a.xlsm

Author

Commented:
Hi Martin:
Is this the same macro, as before?
Software Engineer
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:
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
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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_".

Author

Commented:
Rows 60, 61, for example.  :)

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.
2020-07-10_07-02-11.pngIt 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.
2020-07-10_07-08-01.pngBut 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.

Author

Commented:
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
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Author

Commented:
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
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Author

Commented:
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
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Author

Commented:
Attached shows a compile error.
Now, what?

Capture.PNG
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

Commented:
I simply copied and pasted the code that you sent from your most recent spreadsheet into mine.  So, my sub is your sub.  :)
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:
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
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

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

Author

Commented:
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
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.
2020-07-13_12-05-14.png

Author

Commented:
Okay.  Well, back to the other problem.  Any thoughts on the "compile error"?  What are the next steps?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
But, I do not get a "compile error".
From this that you posted 10 hours ago, what has changed?

Author

Commented:
I meant that I did not get a compile error when I ran the macro in your most recent spreadsheet.  :)
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:
No, last Friday, I got that compile error in my spreadsheet after using the code in the most recent spreadsheet sent.  :)
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

Commented:
sigh......i did that last Friday
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:
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
 
 
Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you, Martin!  It worked!
Software Engineer
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.