×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Conditional Hide Rows or Columns

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!

RE: Conditional Hide Rows or Columns

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.

RE: Conditional Hide Rows or Columns

(OP)
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.

RE: Conditional Hide Rows or Columns

A short VBA will do it.

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

(OP)
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! smile

RE: Conditional Hide Rows or Columns

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!

RE: Conditional Hide Rows or Columns

(OP)
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!

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

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

RE: Conditional Hide Rows or Columns

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

RE: Conditional Hide Rows or Columns

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

RE: Conditional Hide Rows or Columns

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 =IF(B3="1","ROWHIDE",""), and your Worksheet_Change event macro would be like:

CODE

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: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: Conditional Hide Rows or Columns

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

RE: Conditional Hide Rows or Columns

(OP)
Thanks guys! I will check them out!

RE: Conditional Hide Rows or Columns


Try this macro:


CODE

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!

RE: Conditional Hide Rows or Columns

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

RE: Conditional Hide Rows or Columns

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

RE: Conditional Hide Rows or Columns

Actually,

The code you show:

CODE

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

should be:

CODE

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!

RE: Conditional Hide Rows or Columns

thanks PE,

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

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

RE: Conditional Hide Rows or Columns

Does it hide the rest above and below?

RE: Conditional Hide Rows or Columns

it hides all the above but not below

RE: Conditional Hide Rows or Columns

or should I say 3244, 3245, 3246............. and so on are still showing

RE: Conditional Hide Rows or Columns

Is there anything typed into cell au3243?  What about au3244?  Any differences?

RE: Conditional Hide Rows or Columns

au3243 is blank
au3244 has text inputed

RE: Conditional Hide Rows or Columns

If you are looking for a blank cell in column AU, isn't column AU the 47th column?  Maybe that's the problem.

Also, if you have text in au3244, then it wouldn't hide per your macro, correct?

RE: Conditional Hide Rows or Columns

You are correct.... 3244 is a drop down list and I must of miscounted on the column.  "" is correct for blank cells, am I correct?

Many thanks for your help

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources