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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
3,037
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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

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
3,037
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
3,037
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,833
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
3,037
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,833
Office Version
  1. 2010
Platform
  1. Windows
You're welcome.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,493
Messages
5,837,684
Members
430,509
Latest member
steve85215

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