Macro to hide rows based on cell value

Thanks:  0
Likes:  0

# Thread: Macro to hide rows based on cell value

1. ## Macro to hide rows based on cell value

Like to create a macro (button) to hide / unhide rows based on a cell value
Example
A1=No then hide roa A
C1=No then hide row c

2. ## Re: Macro to hide rows based on cell value

How many rows are involved? Or do you want it to apply to the whole spreadsheet?

3. ## Re: Macro to hide rows based on cell value

It would affect any row that has the value of "No" in a cell. It would then only show those rows where the reponse was "Yes"

4. ## Re: Macro to hide rows based on cell value

Try:

Code:
```Sub HideNos()
For i = 1 to Range("A65536").end(xlup).Row
If UCase(Cells(1, i).value) = "NO" then Cells(1, i).entireRow.Hide
Next i
End Sub```
I wrote this then got confused because you say "A1=No then hide row A" but A is a column. Do you want to hide the columns? Or do you want to hide row 1, and if A3=No then hide row 3? My code goes through column A and hides all of the rows where the value of A equals to No.

Hope this helps!

5. ## Re: Macro to hide rows based on cell value

My mistake, if the cell value matches "no" hide that ROW, soory

6. ## Re: Macro to hide rows based on cell value

Then try mine and see what you think.

7. ## Re: Macro to hide rows based on cell value

Changed the code to

Sub HideNos()
For i = 1 To Range("A500").End(xlUp).Row
If UCase(Cells(i, 5).Value) = "NO" Then Cells(i, 1).EntireRow.Hide
Next i
End Sub

But, it goes to debug and highlights the " Cells(i, 1).EntireRow.Hide " segment

8. ## Re: Macro to hide rows based on cell value

Change

.Hide

To

.Hidden = True

Sorry!

9. ## Re: Macro to hide rows based on cell value

Just for your info, I found an alternate method that also works

Sub HideRows()
Dim cell As Range
For Each cell In Range("e:e")
If UCase(cell.Value) = "NO" Then
cell.EntireRow.Hidden = True
End If
Next
End Sub

Thanks for the help, great forum

10. ## Re: Macro to hide rows based on cell value

Five and a half years later, I dont know if the guys that started this thread are still around, but I have a supplimentary question.

Is it possible to adapt the code below so that if it is run more than once it analyses all previously hidden rows and reinstates them if all rows do not contain "No"?

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•