Problem with hiding rows

[Abboskhuja]

Helllo everyone!

I've got several worksheets and I want to hide rows if L,M and N cells in this row are equal to 0, without any values. And I wrote this:

Sub moon()
Dim x As Double
Dim i As Long
Dim ws As Worksheet


Application.ScreenUpdating = False
With ThisWorkbook
For Each ws In .Worksheets
Select Case ws.Name
Case "ХЛ", "КН", "СТ", "СТ авт."
Case Else
With ws
For i = 5 To 250
x = Range("L" & i).Value + Range("M" & i).Value + Range("N" & i).Value
If x = 0 Then
Rows(i).Hidden = True
Else
Rows(i).Hidden = False
End If
Next i
End With
End Select
Next ws
End With
Application.ScreenUpdating = False


End Sub

However, there is an error when I run the code and "Run time error 13: Type Mismatch" keeps appearing. Can you help me? I don't where is the problem.
Thanks in advance.

[p45cal]

The problem is likely to be this line:

x = Range("L" & i).Value + Range("M" & i).Value + Range("N" & i).Value

1. If there's text in one of the cells it can't do an addition (type mismatch).
2. Summing the 3 cells may not tell you if all three values are 0; the 3 values could, for example, be 1, -1, 0.

You may be better off with the likes of:

If .Range("L" & i).Value = 0 And .Range("M" & i).Value = 0 And .Range("N" & i).Value = 0 Then

but that will not hide rows where there is text in one of those cells - maybe that's what you want?

If you want to hide those rows with text in one or more of those cells too, then perhaps:

If (.Range("L" & i).Value = 0 Or TypeName(.Range("L" & i).Value) = "String") And (.Range("M" & i).Value = 0 Or TypeName(.Range("M" & i).Value) = "String")  And (.Range("N" & i).Value = 0 Or TypeName(.Range("N" & i).Value) = "String")  Then

If you only want to hide rows where the three cells are "without any values" then it could be as simple as:

If Application.CountBlank(.Range("L" & i & ":N" & i)) = 3 Then

In full (you don't need the x variable):

Sub moon()
Dim i As Long
Dim ws As Worksheet

Application.ScreenUpdating = False
With ThisWorkbook
  For Each ws In .Worksheets
    Select Case ws.Name
      Case "??", "??", "??", "?? ???."   '<<<<you will need to correct this!!
      Case Else
        With ws
          For i = 5 To 250
            If Application.CountBlank(.Range("L" & i & ":N" & i)) = 3 Then
              .Rows(i).Hidden = True
            Else
              .Rows(i).Hidden = False
            End If
          Next i
        End With
    End Select
  Next ws
End With
Application.ScreenUpdating = True
End Sub

3. You must have a dot before each Range reference and before each Rows reference within the With ws ... End With. Otherwise the wrong sheet will be processed (only the active sheet).

4. I suspect you want that last line to be:
Application.ScreenUpdating = True

5. Whichever If phrase you want to use, you can shorten your code to the likes of:

Sub moon()
Dim i As Long
Dim ws As Worksheet

Application.ScreenUpdating = False
With ThisWorkbook
  For Each ws In .Worksheets
    Select Case ws.Name
      Case "??", "??", "??", "?? ???."   '<<<<you will need to correct this!!
      Case Else
        With ws
          For i = 5 To 250
            .Rows(i).Hidden = Not (Application.CountBlank(.Range("L" & i & ":N" & i)) = 3)
          Next i
        End With
    End Select
  Next ws
End With
Application.ScreenUpdating = True
End Sub

6. Finally, note that the Case statement above hasn't properly copied your sheet names so you'll need to correct that.

[Abboskhuja]

Thank you vey much!