VBA for Hiding or Removing all Zero Values from Specific Worksheet Tabs

[njpamdnc]

Hello...My name is Robert, and I am an intermediate school math teacher who is trying to remove zero values from certain worksheets in my Excel gradebook. I know for a fact the Macro below this message executes. However, I need the macro to execute for multiple and specific worksheet tabs. The names of the worksheet tabs being referenced begin with q1, q2, q3 and q4. Therefore, I'm thinking maybe the VBA syntax will be q1*, q2*, q3* and q4*.

Sub Hide0()
ActiveWindow.DisplayZeros = False
End Sub

 

[johnnyL]

One way to do it:

Sub HideZeros()
'
    Dim ws As Worksheet
'
    Application.ScreenUpdating = False
'
    For Each ws In ThisWorkbook.Worksheets
        If (ws.Name Like "q1*" Or ws.Name Like "q2*" Or ws.Name Like "q3*" Or ws.Name Like "q4*") Then
            ws.Activate
            ActiveWindow.DisplayZeros = False
        End If
    Next
'
    Application.ScreenUpdating = True
End Sub

 

[kevin9999]

Another option (if your intention was to replace the zero values with an empty string)

Option Explicit
Option Compare Text
Sub RidMeOfZeroes()
Dim i As Long, j As Long, k As Long, s As String, q, a
Dim ws As Worksheet, x As String
Application.ScreenUpdating = False

q = Array("q1*", "q2*", "q3*", "q4*")
For Each ws In ThisWorkbook.Worksheets
    For i = LBound(q) To UBound(q)
        If ws.Name Like q(i) Then
            x = ws.UsedRange.Cells(1).Address
            a = ws.UsedRange
                For j = LBound(a, 1) To UBound(a, 1)
                    For k = LBound(a, 2) To UBound(a, 2)
                        s = a(j, k)
                        If s = "0" Then a(j, k) = ""
                    Next k
                Next j
            ws.Range(x).Resize(UBound(a, 1), UBound(a, 2)).Value2 = a
        End If
    Next i
Next ws

Application.ScreenUpdating = True
End Sub

 

[shinigamilight]

Sub zeroes()
            Dim wk As Worksheet
            
            For Each wk In Worksheets
                    If UCase(Left(wk.Name, 1)) = "Q" Then
                        wk.Cells.Replace 0, "", xlPart, xlByRows, False
                    End If
            Next wk
            
End Sub

 

[njpamdnc]

Another option (if your intention was to replace the zero values with an empty string)

Option Explicit
Option Compare Text
Sub RidMeOfZeroes()
Dim i As Long, j As Long, k As Long, s As String, q, a
Dim ws As Worksheet, x As String
Application.ScreenUpdating = False

q = Array("q1*", "q2*", "q3*", "q4*")
For Each ws In ThisWorkbook.Worksheets
    For i = LBound(q) To UBound(q)
        If ws.Name Like q(i) Then
            x = ws.UsedRange.Cells(1).Address
            a = ws.UsedRange
                For j = LBound(a, 1) To UBound(a, 1)
                    For k = LBound(a, 2) To UBound(a, 2)
                        s = a(j, k)
                        If s = "0" Then a(j, k) = ""
                    Next k
                Next j
            ws.Range(x).Resize(UBound(a, 1), UBound(a, 2)).Value2 = a
        End If
    Next i
Next ws

Application.ScreenUpdating = True
End Sub

Hello kevin9999...Your VBA works too well...LOL In other words, it also removes the formulas that produce the zero values. I don't want the formulas removed leaving completely empty cells. I simply want the zeros removed. Maybe I should have only used "Hiding" rather than "Removing" in my subject line of my thread post.

 

[jolivanes]

Re: "it also removes the formulas that produce the zero values"
I've looked and re-read your first post but I can't find where you mention that the 0's (zeros) are the result of a formula.
Maybe you can enlighten me.

You might need to change your formulae to show "" in the event that the calculation calculates to be zero (0)

 

[rlv01]

Hello kevin9999...Your VBA works too well...LOL In other words, it also removes the formulas that produce the zero values. I don't want the formulas removed leaving completely empty cells. I simply want the zeros removed. Maybe I should have only used "Hiding" rather than "Removing" in my subject line of my thread post.

@johnnyL offered a reasonable solution. Why not use that?

 

[kevin9999]

Hello kevin9999...Your VBA works too well...LOL In other words, it also removes the formulas that produce the zero values. I don't want the formulas removed leaving completely empty cells. I simply want the zeros removed. Maybe I should have only used "Hiding" rather than "Removing" in my subject line of my thread post.

Yes, I was focussing on the "Removing" part of your post's title. If you just want to hide them, then post #2 provides the simplest solution.

 

[njpamdnc]

Yes, I was focussing on the "Removing" part of your post's title. If you just want to hide them, then post #2 provides the simplest solution.

Post #2 works only for the active window I am using. If you look at the image I attached to this message, you will see 16 window names for my gradebook. My gradebook is extremely large. Therefore, I decided to use 16 windows to make navigation easier for me. Post #2 only works for the active window. The screenshot shows zero values for the active window(2nd window out of a total of 16 windows): gradebook(virtual) copy:2 because the macro only worked for the (1st window) gradebook(virtual) copy:1. The remaining 14 windows also have zero values as well. In order for the Post #2 macro to work for me, it would have to loop throughout the remaining 15 windows after processing the first window. Otherwise, I have to run the macro 15 additional times.

Your Post #3 is the most ideal for me because your macro processes throughout the entire workbook at once. All I need is the hiding zeros from Post #2 to be combined with the entire workbook being processed from Post #3.

 

[rlv01]

Post #2 works only for the active window I am using. If you look at the image I attached to this message, you will see 16 window names for my gradebook. My gradebook is extremely large. Therefore, I decided to use 16 windows to make navigation easier for me. Post #2 only works for the active window. The screenshot shows zero values for the active window(2nd window out of a total of 16 windows): gradebook(virtual) copy:2 because the macro only worked for the (1st window) gradebook(virtual) copy:1. The remaining 14 windows also have zero values as well. In order for the Post #2 macro to work for me, it would have to loop throughout the remaining 15 windows after processing the first window. Otherwise, I have to run the macro 15 additional times.

A post #2 variant still seems like the simplest method.

Sub HideZeros2()
'
    Dim ws As Worksheet
    Dim W As Window
    Application.ScreenUpdating = False
    '
    For Each W In Application.Windows
        If Left(W.Caption, 9) = "gradebook" Then
            W.Activate
            For Each ws In ThisWorkbook.Worksheets
                If (ws.Name Like "q1*" Or ws.Name Like "q2*" Or ws.Name Like "q3*" Or ws.Name Like "q4*") Then
                    ws.Activate
                    ActiveWindow.DisplayZeros = False
                End If
            Next ws
        End If
    Next W
    Application.ScreenUpdating = True
End Sub