Macro to Hide all Rows that have a Defined word in column AC?

[tonywatsonhelp]

Hi Everyone
I Need a macro that can look in active sheet down Column AC and any rows it finds that contain the word "Private" to Hide

I've been trying but can't get close, please can someone help me as I really need to be able to easily hide these areas and it needs to be with a macro!

Thanks

Tony

 

[kasanari]

Hello,

This should help:

Sub hiderows()
Dim lastrow As Long
lastrow = ActiveSheet.Range("AC" & Rows.Count).End(xlUp).Row
For i = lastrow To 1 Step -1
If Range("AC" & i).Value = "Private" Then
Rows(i).Hidden = True
End If
Next

End Sub

 

[tonywatsonhelp]

Brilliant Kasanari, thank you so much, this is exactly what i wanted -)

Thanks

Tony

 

[tonywatsonhelp]

Hi thanks for your help but I tried to create an UNhide as well (seemed simple at the time)
but it doesn't work,
The hide works?
Can someone tell me were i went wrong?

Sub zzunhiderowspg1()
Dim lastrow As Long
lastrow = ActiveSheet.Range("AC" & Rows.Count).End(xlUp).Row
For i = lastrow To 1 Step -1
If Range("AC" & i).Value = "PG1" Then
Rows(i).Hidden = False
End If
Next


End Sub

PS i plan to use to code and have several comand like "Private" "Boss Only" etc so when it unhides I only want it to unhide the cell that are "Private" or in this case PG1 just for testing?

Thanks

Tony

 

[shg]

How about ...

Sub Main()
  HideUnhide Columns("AC"), "Bob", False
  HideUnhide Columns("AV"), "PG1", True
End Sub

Sub HideUnhide(r As Range, sInp As String, bHide As Boolean)
  Dim cell As Range
  
  For Each cell In Intersect(r, r.Worksheet.UsedRange).Cells
    If cell.Value = sInp Then cell.EntireRow.Hidden = bHide
  Next cell
End Sub

Be aware that comparisons are case-sensitive.

 

[kasanari]

Why would you have rows already hidden with those different values if only "Private" was hidden in the same column? If you want it to just hide a different set of items, you can just add the following to the beginning to unhide all and hide based on a different criterion:

ActiveSheet.Cells.EntireRow.Hidden = False


The post above is more advanced and works too if you have a bunch of different things to compare in various columns

 

[tonywatsonhelp]

thanks guys,
shg, your suggestion is great thank you i will play about with it for a while,
kasanari, thank you, that's a great idea as i will only want to unhide when i'm starting again so if i unhide all then just click on the relevant names to hide!

thanks so much i would never have got there thats excellent thank you

 

[shg]

You're welcome.