Macro to hide rows based on cell value

[UHsoccer]

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

 

[Audiojoe]

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

 

[UHsoccer]

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"

 

[LTunnicliffe]

Try:

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!

 

[UHsoccer]

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

 

[LTunnicliffe]

Then try mine and see what you think.

 

[UHsoccer]

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

 

[LTunnicliffe]

Change

.Hide

To

.Hidden = True

Sorry!

 

[UHsoccer]

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

 

[fanboy]

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"?