Hiding rows dynamically based on if statement VBA

user_16036523 0
Viewed 201

I hope someone can help me out with the following:

I am trying to dynamically hide rows based on values on another worksheet. However, I am getting the error "Next without For" but I am also uncertain as to the functionality of my code. I would very much appreciate it if someone could help me out here.

Sub Update()

Dim x As Integer

For x = 8 To 22
    If Sheets("1").Cells(x, 3).Value = 0 Then
    Sheets("2").Rows("x - 3 : x - 3").Select
    Selection.EntireRow.Hidden = True
    Else
    Sheets("2").Rows("x - 3 : x - 3").Select
    Selection.EntireRow.Hidden = False
    End if
Next x

End Sub

EDIT (SOLUTION) The code that works is:

Sub Update()

Dim x As Integer

For x = 8 To 22
    If Sheets("1").Cells(x, 3).Value = 0 Then
    Sheets("2").Rows((x - 3) & ":" & (x - 3)).Hidden = True
    Else
    Sheets("2").Rows((x - 3) & ":" & (x - 3)).Hidden = False
    End If
Next x

End Sub

Or

Dim x As Long

For x = 8 To 22
    Sheets("2").Rows(x - 3).Hidden = Sheets("1").Cells(x, 3).Value = 0
Next x
vba excel
2 Answers

You can change this part to simplify the code, since you don't have to select the worksheet:

 If Sheets("1").Cells(x, 3).Value = 0 Then
    Sheets("2").Rows("x - 3 : x - 3").Select
    Selection.EntireRow.Hidden = True
    Else

To:

If Sheets("1").Cells(x, 3).Value = 0 Then
Sheets("2").Rows(x).Hidden = True
Else
user_158978610

  1. Anything between quotes will be considered as a string. So "x-3:x-3" is a string. As @DS_London mentioned use (x - 3) & ":" & (x - 3)

  2. Having said that Sheets("2").Rows((x - 3) & ":" & (x - 3)).Hidden can be written Sheets("2").Rows(x - 3).Hidden

Alternative way to achieve what you want

Dim x As Long

For x = 8 To 22
    Sheets("2").Rows(x - 3).Hidden = Sheets("1").Cells(x, 3).Value = 0
Next x
user_11405790
Related