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

#### psg2002

##### New Member
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.

VBA Code:
``````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``````

#### Attachments

• SHEET1.png
22.9 KB · Views: 4

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Joe4

See if this does what you want:
VBA Code:
``````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

##### New Member
See if this does what you want:
VBA Code:
``````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?

#### psg2002

##### New Member

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:
VBA Code:
``````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``````

Last edited:

#### psg2002

##### New Member
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:
VBA Code:
``````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.

Note that I had an extra unnecessary line in my original code.
This line:
VBA Code:
``    Set rng = Range("A4:G4")``
can be removed.

I went back and removed it from the code above.

Replies
1
Views
65
Replies
7
Views
100
Replies
12
Views
430
Replies
9
Views
93
Replies
1
Views
215

1,127,040
Messages
5,622,343
Members
415,894
Latest member
silverhaze

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back