Conditional Hide Rows or Columns 1

[jacky89]

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!

 

[electricpete]

a few thoughts (may not be what you're looking for)...

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.

 

[jacky89]

I know about the white font conditional formatting but that is not what I want because the conditional formatting does not support multiple font types (ie. subscripts & superscripts not work well with conditional formatting). Also there are too many rows I need to hide so the white font trick will leave a huge blank space in the spreadsheets.

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.

 

[IFRs]

A short VBA will do it.

 

[yogia]

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

 

[jacky89]

Thanks for the reply IFRs and yogia,

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!

 

[IFRs]

Try recording a macro that does what you want - Start Recording, Do What you want, Stop recording. Then go to the VBA editor and see what it did. Modify as needed. Annotate, Save and use it!

 

[jacky89]

I recorded a command and this is the code:

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!

 

[yogia]

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 ...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column <> 1 Then Exit Sub
    Target.EntireRow.Hidden = True
End Sub

I hope this helps. You will get much more help with macros if you post in the VBA forum of tek-tips.com



Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[jacky89]

Well actually, I was wondering if I can make a user-defined function that would trigger the hide-row macro.

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,"")

 

[handleman]

You can't alter the structure or format of a sheet with a user-defined function.

 

[joerd]

You could use Yogia's approach, and use an IF function to set a flag if you want the column or row to be hidden. So, the macro will be executed every time the worksheet changes, then look for all cells that have "ROWHIDE" or "COLUMNHIDE" in them, and hide rows/columns accordingly.
So, your worksheet functions would be something like [tt]=IF(B3="1","ROWHIDE","")[/tt], and your Worksheet_Change event macro would be like:

Private Sub Worksheet_Change(ByVal Target As Range)
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

Note that this will slow down your worksheet, since it will be repeated all the time as you are working with the data. If you don't mind some user interaction, you can define a button that triggers the macro.

Alternatively, look at the Filter/Autofilter options: menu Data/Filter/AutoFilter.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[zuccus]

Jacky,

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

 

[jacky89]

Thanks guys! I will check them out!

 

[PEStructural]

Try this macro:

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 = "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!

 

[jacky89]

Thank you very much Pestructural! I really appreciate everybody's help!

 

[Harley78]

Let me see if I got this straight,,,, sorry I am a new to macro building....

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

 

[PEStructural]

Actually,

The code you show:

Rows("3:65203").EntireRow.Hidden = True

should be:

Rows("3").EntireRow.Hidden = True

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!

 

[Harley78]

thanks PE,

What I am doing is hiding complete row in which any cell that is in column au is blank

 

[Harley78]

However, what is weird is that I created a command button to hide rows with the following code and it doesn't hide 3244-3273,

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