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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,742
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

kasanari

New Member
Joined
May 29, 2015
Messages
12
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

Well-known Member
Joined
Feb 24, 2014
Messages
2,742
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Brilliant Kasanari, thank you so much, this is exactly what i wanted -)

Thanks

Tony
 

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,742
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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?
Code:
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,784
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

How about ...

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

New Member
Joined
May 29, 2015
Messages
12
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
 
Last edited:

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,742
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,784
Office Version
  1. 2010
Platform
  1. Windows
You're welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,594
Messages
5,637,299
Members
416,963
Latest member
zazama

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