How to create loop: hide rows if range is equal to value

[psg2002]

Hi,

I am not familiar with the looping system. How can I create a loop where I am able to hide rows if the range B:F = "no data"? I want the loop to start from row 4 to 16.

this is the current code that i have, where i was doing it 1 by 1.

Sub Macro2()

Dim m As Excel.Workbook
Set m = ThisWorkbook

                                   
If m.Worksheets("sheet1").Range("B4:F4").Value = "no data" Then
Rows(4).Select
Selection.EntireRow.Hidden = True
End If
       
End Sub

Appreciate the help in advance!

 

[Joe4]

See if this does what you want:

Sub MyHideRows()

    Dim r As Long
    Dim rng As Range
    Set rng = Range("A4:G4")
    
    Sheets("Sheet1").Activate
    
    For r = 4 To 16
        Set rng = Range("B" & r & ":F" & r)
        If WorksheetFunction.CountBlank(rng) = 5 Then Rows(r).Hidden = True
    Next r
    
End Sub

 

[psg2002]

See if this does what you want:

Sub MyHideRows()

    Dim r As Long
    Dim rng As Range
    Set rng = Range("A4:G4")
   
    Sheets("Sheet1").Activate
   
    For r = 4 To 16
        Set rng = Range("B" & r & ":F" & r)
        If WorksheetFunction.CountBlank(rng) = 5 Then Rows(r).Hidden = True
    Next r
   
End Sub

Hi thanks for replying. For this line " If WorksheetFunction.CountBlank(rng) = 5 Then Rows(r).Hidden = True" , what does the '5' doing?

 

[Joe4]

It is counting the number of blank cells on that row, between columns B and F.
It uses the Excel COUNTBLANK function. See here for more details: MS Excel: How to use the COUNTBLANK Function (WS)

 

[psg2002]

It is counting the number of blank cells on that row, between columns B and F.
It uses the Excel COUNTBLANK function. See here for more details: MS Excel: How to use the COUNTBLANK Function (WS)

Oh I see. I tried running the macro, but nothing happen. I believe it may because I do not have any blank cells. I am trying to hide ranges with "no data". how can i adjust the code to pick this up? thanks

 

[Joe4]

Oh I see. I tried running the macro, but nothing happen. I believe it may because I do not have any blank cells. I am trying to hide ranges with "no data". how can i adjust the code to pick this up? thanks

My apologies. I misunderstood. I think you meant that there was no data, not literally the text "no data" (I should have looked at your picture first!).
Try this:

Sub MyHideRows()

    Dim r As Long
    Dim rng As Range
   
    Sheets("Sheet1").Activate
   
    For r = 4 To 16
        Set rng = Range("B" & r & ":F" & r)
        If WorksheetFunction.CountIf(rng, "no data") = 5 Then Rows(r).Hidden = True
    Next r
   
End Sub

 

[psg2002]

My apologies. I misunderstood. I think you meant that there was no data, not literally the text "no data" (I should have looked at your picture first!).
Try this:

Sub MyHideRows()

    Dim r As Long
    Dim rng As Range
    Set rng = Range("A4:G4")
   
    Sheets("Sheet1").Activate
   
    For r = 4 To 16
        Set rng = Range("B" & r & ":F" & r)
        If WorksheetFunction.CountIf(rng, "no data") = 5 Then Rows(r).Hidden = True
    Next r
   
End Sub

Thank you so much Joe4, it runs perfectly!

 

[Joe4]

You are welcome.
Glad to help!


Note that I had an extra unnecessary line in my original code.
This line:

    Set rng = Range("A4:G4")

can be removed.

I went back and removed it from the code above.