Hide multiple row if their requirement is met-CodePudding

I am new to coding in anything, this project is the first time I have coded. I am trying to hide multiple row based on individual requirement. The requirement is if in a specific cell of the same row there is a space or is empty, the row will be hidden, if it is hidden and there is anything else, the row will be shown. The code need to work on specific worksheet as I have multiple worksheet where there is row to hide or columns to hide at different place.

There are 2 different pieces of code that I tried which don't work.

This picture represent the Excel sheet I am currently trying to hide row:

worksheet

My goal is to hide row between 8 to 37 if there is there is a space or if it is empty, depending what the code inside the cell point at for the cell A8 to A37. if I activate the code, in the image only the row 8, 9 and 10 should be visible, 11 to 37 should be hidden.

So far I have tried these two pieces of code:

    Sub C1()
       
    Set ws = ActiveWorkbook.Worksheets("FR-3-06_Jeux Prod.")
    
    Dim C As range

        For Each C In range("A8:A37")
    
        If C.Value = " " Then
    
            C.EntireRow.Hidden = True
         
            Else
    
                If C.Value = Empty Then
    
                    C.EntireRow.Hidden = True
            
                Else
    
                    C.EntireRow.Hidden = False
    
                End If
        
        End If
        
    Next C
    

End Sub

This code work as intended except that it is not tied to a sheet. "Set ws = ActiveWorkbook.Worksheets("FR-3-06_Jeux Prod.")" is not working as well as a couple other code I tried, they point to an error. So when I try to use this code it will work on the active sheet and not "FR-3-06_Jeux Prod."

    Sub Hide_column_and_Row_F_3_6()

    Dim NbreLigne As Integer
    Dim tableau As range
    
    Set wrkshtDoc = ActiveWorkbook.Worksheets("FR-3-06_Jeux Prod.")
    Set tableau = wrkshtDoc.range("A8:A37")
    
    
    NbreLigne = tableau.Rows.Count

     For k = 1 To NbreLigne
                If tableau(1, k) = " " Then
                    tableau(1, k).EntireRow.Hidden = True
                ElseIf tableau(1, k) = Empty Then
                    tableau(1, k).EntireRow.Hidden = True
                Else
                 tableau(1, k).EntireRow.Hidden = False
                End If
            Next k

End Sub

This code only works as intended when I try to hide columns as in replace "row" in the code with "columns". There is sheet in my file where is it columns I need to hide and since this code is working I tried to reuse it... what it is currently doing is hiding row with "test", line 8 only. It wont hide the empty cell.

what would be the error or what would be needed to hide row with the requirement? I know that code #2 work with columns...

CodePudding user response：

You are almost there with code1, you only need to add: For each C in ws.Range("A8:A38")

Because you add ws. in front of the Range, it knows which sheet to apply it on.

Good luck!

CodePudding user response：

Hide Blank Rows

Option Explicit

Sub HideBlankRows()
       
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("FR-3-06_Jeux Prod.")
    
    Dim Cell As Range

    For Each Cell In ws.Range("A8:A37").Cells
        Cell.EntireRow.Hidden _
            = IIf(Len(Trim(CStr(Cell.Value))) = 0, True, False)
    Next Cell

End Sub