Conditional Hide Rows or Columns
Conditional Hide Rows or Columns
(OP)
Can anyone tell me how to hide certain rows or columns when certain conditions are triggered? I've spent 10+ hours searching online but couldn't find any information on how to do this. Any help would be greatly appreciated!
THanks!
THanks!





RE: Conditional Hide Rows or Columns
with conditional formatting, you can turn the font white which sort of hides the data.
you can hide rows fairly easily with the autofilter feature
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Conditional Hide Rows or Columns
I do not see autofilter. What is it? Does it require manually clicking on the toolbars to get it to work? If so, that is not what I want. I want a way for the spreadsheet to hide rows and columns automatically without the user having to manually click filter.
RE: Conditional Hide Rows or Columns
RE: Conditional Hide Rows or Columns
Hi jacky89:
To add to what IFRs has said, you may want to consider using a Worksheet_Change event ... when a condition is fulfilled it will automatically trigger the routine to hide the rows and columns per your specification.
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com
RE: Conditional Hide Rows or Columns
I am not very macro literate. Can you tell me how to use Worksheet_Change event and how to hide the rows and columns?
If you can provide me with an example macro on how this can be done, I would greatly, greatly appreciate it!
RE: Conditional Hide Rows or Columns
RE: Conditional Hide Rows or Columns
Sub Macro1()
'
' Macro1 Macro
' hide row
'
'
Rows("54:54").Select
Selection.EntireRow.Hidden = True
End Sub
How do I make this a function that I can use in a cell? For example I want this set of code to be named as function Hiderow54. I want to be able to write a function in a cell such as if(b3="1",hiderow54,"")
Can you give me some tips? Sorry, I'm clueless with macros..
Thanks!
RE: Conditional Hide Rows or Columns
Hi Jacky89:
Let us consider using a Worksheet_SelectionChange event. Here is the scenario ... if I select a cell in column A then that row will be hidden. However if I select a cell in any other column nothing will happen ...
CODE
If Target.Column <> 1 Then Exit Sub
Target.EntireRow.Hidden = True
End Sub
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com
RE: Conditional Hide Rows or Columns
For example I want this set of code to be named as function HIDE. I want to be able to write a function that can be used in a cell such as if(b3="1",HIDE,"")
RE: Conditional Hide Rows or Columns
RE: Conditional Hide Rows or Columns
So, your worksheet functions would be something like =IF(B3="1","ROWHIDE",""), and your Worksheet_Change event macro would be like:
CODE
Dim r As Range, fr As Range
'screen off for speed
Application.ScreenUpdating = False
'reset the worksheet: show all rows/columns
Me.Rows.Hidden = False
Me.Columns.Hidden = False
Set r = Me.UsedRange.Find("ROWHIDE", , xlValues, xlWhole, , xlNext, False, False)
If Not r Is Nothing Then
Set fr = r
Do
r.EntireRow.Hidden = True
r.FindNext
Loop While r.Address <> fr.Address
End If
Set r = Me.UsedRange.Find("COLUMNHIDE", , xlValues, xlWhole, , xlNext, False, False)
If Not r Is Nothing Then
Set fr = r
Do
r.EntireColumn.Hidden = True
r.FindNext
Loop While r.Address <> fr.Address
End If
Set fr = Nothing
Set r = Nothing
Application.ScreenUpdating = True
End Sub
Alternatively, look at the Filter/Autofilter options: menu Data/Filter/AutoFilter.
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Conditional Hide Rows or Columns
Mr Excel has an example simliar to how it was done in Lotus 123 way back when. This hides the rows only when you print them.
http://www.mrexcel.com/tip033.shtml
I have used this in the past with much success for hiding rows that are zero in my estimating spreadsheets.
Zuccus
RE: Conditional Hide Rows or Columns
RE: Conditional Hide Rows or Columns
Try this macro:
CODE
' Run the macro DidCellsChange any time a entry is made in a
' cell in Sheet1.
ThisWorkbook.Worksheets("Sheet1").OnEntry = "DidCellsChange"
End Sub
Sub DidCellsChange()
Dim KeyCells As String
' Define which cells should trigger the KeyCellsChanged macro.
KeyCells = "B3"
' If the Activecell is one of the key cells, call the
' KeyCellsChanged macro.
If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
Is Nothing Then KeyCellsChanged
End Sub
Sub KeyCellsChanged()
'
Application.ScreenUpdating = False 'Turning off screen
'
If Range("B3") = 1 Then
Rows("9:12").EntireRow.Hidden = True
Range("B3").Select
Else
Rows("9:12").EntireRow.Hidden = False
Range("B3").Select
End If
'
Application.ScreenUpdating = True
'
End Sub
You can obviously go into the visual basic editor and change the worksheet name from "Sheet1" to whatever you'd like. Likewise, you can change the reference to "B3" to whatever cell you'd like. Same thing goes for the row numbers.
I have also uploaded a spreadsheet that contains the working macro:
http://pestructural.4shared.com/
Good luck!
RE: Conditional Hide Rows or Columns
RE: Conditional Hide Rows or Columns
If there is a blank in say cell AU3 and I want to hide the entire row. Then I would change to reflect the following, So sorry about my ignorance!
Sub auto_open()
' Run the macro DidCellsChange any time a entry is made in a
' cell in Sheet1.
ThisWorkbook.Worksheets("Sheet1").OnEntry = "DidCellsChange"
End Sub
Sub DidCellsChange()
Dim KeyCells As String
' Define which cells should trigger the KeyCellsChanged macro.
KeyCells = "AU3"
' If the Activecell is one of the key cells, call the
' KeyCellsChanged macro.
If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
Is Nothing Then KeyCellsChanged
End Sub
Sub KeyCellsChanged()
'
Application.ScreenUpdating = False 'Turning off screen
'
If Range("au3") = "" Then
Rows("3:65203").EntireRow.Hidden = True
Range("au3").Select
Else
Rows("3:65203").EntireRow.Hidden = False
Range("au3").Select
End If
'
Application.ScreenUpdating = True
'
End Sub
RE: Conditional Hide Rows or Columns
The code you show:
CODE
should be:
CODE
The problem with this would be if you hide the row that your cell is in, then you have to unhide the row to input a different number.
Also, the "" doesn't work in the macro unless you phsically type ="" in the cell.
If this is for hiding a row that contains a formula that changes the cell to "", then the code would be a little different.
Please repost if you need help with that situation.
Good luck!
RE: Conditional Hide Rows or Columns
What I am doing is hiding complete row in which any cell that is in column au is blank
RE: Conditional Hide Rows or Columns
Private Sub CommandButton1_Click()
Dim LastRow As Long, i As Long
LastRow = Cells(3296, 46).End(xlUp).Row
Application.ScreenUpdating = False
For i = LastRow To 2 Step -1
If Cells(i, 46).Value = "" Then Cells(i, 46).EntireRow.Hidden = True
Next i
Application.ScreenUpdating = True
End Sub
RE: Conditional Hide Rows or Columns
RE: Conditional Hide Rows or Columns
RE: Conditional Hide Rows or Columns
RE: Conditional Hide Rows or Columns
RE: Conditional Hide Rows or Columns
au3244 has text inputed
RE: Conditional Hide Rows or Columns
Also, if you have text in au3244, then it wouldn't hide per your macro, correct?
RE: Conditional Hide Rows or Columns
Many thanks for your help