Forum Discussion

BrianBrucker's avatar
BrianBrucker
Copper Contributor
Dec 28, 2023

VBA Code Hide rows

I have a excel work sheet where there is 200 rows. If column k is true I want to hide the rows from the value of column S to the value in column U.  if K is false the selected rows are visible.

is this the correct syntax to do th

 

Sub Hide_Rows_Based_On_Cell_Value()

StartRow = 19

EndRow = 200

ColNum = 10

For i = StartRow To EndRow

If Cells(i, ColNum).Value = “True” Then

Rows("$S$"i":$U$"i").EntireRow.Hidden = False
Else
Rows("$S$"i":$U$"i").EntireRow.Hidden = True
End If

Next i
End Sub

excel
Macros and VBA

1 Reply

  • djclements's avatar
    djclements
    Silver Contributor
    Dec 29, 2023

    BrianBrucker Something along these lines should do the trick:

     

    Sub ShowHideRows()
    Dim ws As Worksheet, i As Long
    Set ws = Application.ActiveSheet
    Application.ScreenUpdating = False
    For i = 19 To 200
        If ws.Cells(i, 11).Value Then
            ws.Rows(i).Hidden = False
        Else
            ws.Rows(i).Hidden = True
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

     

    Since the variables StartRow, EndRow and ColNum are only used once, I didn't bother defining them. By the way, column K is 11, not 10, so I changed the column number to 11 in the code above. Also, ScreenUpdating was turned off before iterating through the rows in order to improve performance.

     

    If you have any questions, please let me know. Cheers!

Resources