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

psg2002

New Member
Joined
Feb 14, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

Appreciate the help in advance!
 

Attachments

  • SHEET1.png
    SHEET1.png
    22.9 KB · Views: 4

Some videos you may like

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,503
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 14, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 14, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,503
Office Version
  1. 365
Platform
  1. Windows
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:
Solution

psg2002

New Member
Joined
Feb 14, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,503
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad to help!
:)

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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top