View Full Version : [SOLVED:] Problem with hiding rows
Abboskhuja
11-25-2019, 05:31 AM
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
11-25-2019, 07:37 AM
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 ThenIn 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
11-26-2019, 10:07 AM
Thank you vey much! :thumb
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.