Hide Columns IF Macro or Formula

[gottimd]

I am trying to figure out a way how to hide certain Columns if, in a given Financial quarter, periods have already gone by. As in if you are in Q3, Periods 1-9 have gone by and you don't need to forecast. I have a cell which the end user enters which quarter is completed, but rather than manual hide columns each period the forecast model is sent out, I was hoping that there was either a macro that I could write in Vbasic for the user to press to hide columns that don't need to be forecasted, or to have a check box or something of the sort to hide specific periods data that has already occurred? I used the row macro that someone posted here earlier to hide rows ("hide rows if") that have zero values, and I tried to switch it to columns but the range didn't work. Can anyone help me out, so I can have a button to press based on which quarter ended (i.e. If in Q2, hide columns with Periods 1-6 data), to hide certain columns of data that do not need to be forecasted?

 

[kkknie]

Here is a piece of code that will hide columns based on the value entered in a cell. It uses cell A1 and hides columns B-D or B-G based on the entry 1Q or 2Q in the cell.

The macro needs to be placed in the code section for the worksheet. Here's how to do this:

1. Select Tools | Macro | Visual Basic Editor
2. In the upper left hand corner of the VB editor you should see the project explorer (if you don't, hit Cntrl-R to show it).
3. Find the entry for your workbook (it should say something like VBAProject (Book1) where Book1 is your workbook name).
4. Drill down the list until you find the sheet name you want and double-click it.
5. The code window should appear with nothing in it.
6. Paste the following code.

<PRE>Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count = 1 And Target.Address = "$A$1" Then

If LCase(Target.Value) = "1q" Then
Columns("B:D").EntireColumn.Hidden = True
ElseIf LCase(Target.Value) = "2q" Then
Columns("B:G").EntireColumn.Hidden = True
Else
Columns("B:M").EntireColumn.Hidden = False
End If

End If

End Sub</PRE>Now every time you change the value in cell A1, the code will run and either hide some columns or show all of them based on the value entered (target.value).

Note that the code is incomplete as it only hides first and second quarter columns, but it should be a fair start for you to work with. You will need to add additional ElseIf statements and possibly change the cell for the data entry.

Hope this helps,

K

 

[gottimd]

Thank you very much, I will get to work on it now.

 

[gottimd]

I have created the Macro but it is debugging at the part that says:
If Target.Cells.Count = 1 And Target.Address = "$B$102" Then
Here is what the Macro in total looks like, does it make sense?
Sub HideColumn()
'
' HideColumn Macro
' Macro recorded 7/29/2002 by Sodexho
'
' Keyboard Shortcut: Ctrl Shift + C
'
'Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count = 1 And Target.Address = "$B$102" Then

If LCase(Target.Value) = "0" Then
Columns("X").EntireColumn.Hidden = True
Columns("AC").EntireColumn.Hidden = True
Columns("AH").EntireColumn.Hidden = True
Columns("AM").EntireColumn.Hidden = True
ElseIf LCase(Target.Value) = "1" Then
Columns("Y").EntireColumn.Hidden = True
Columns("AC").EntireColumn.Hidden = True
Columns("AH").EntireColumn.Hidden = True
Columns("AM").EntireColumn.Hidden = True
ElseIf LCase(Target.Value) = "2" Then
Columns("Y").EntireColumn.Hidden = True
Columns("AD").EntireColumn.Hidden = True
Columns("AH").EntireColumn.Hidden = True
Columns("AM").EntireColumn.Hidden = True
ElseIf LCase(Target.Value) = "3" Then
Columns("Y").EntireColumn.Hidden = True
Columns("AD").EntireColumn.Hidden = True
Columns("AI").EntireColumn.Hidden = True
Columns("AM").EntireColumn.Hidden = True
ElseIf LCase(Target.Value) = "4" Then
Columns("Y").EntireColumn.Hidden = True
Columns("AD").EntireColumn.Hidden = True
Columns("AI").EntireColumn.Hidden = True
Columns("AN").EntireColumn.Hidden = True
Else
Columns("D:AW").EntireColumn.Hidden = False
End If

End If

End Sub

 

[kkknie]

I knew I forgot to explain something...

The macro I sent was not a normal macro, but an event procedure attached to the worksheet's Change event. Basically, the macro runs whenever any value in the worksheet is changed (the reason I said to put it in the worksheet's code section).

If you want to attach it to a button (or to a shortcut key), you would simply remove the<PRE>If Target.Cells.Count = 1 And Target.Address = "$B$102" Then</PRE>statement (along with the End If) and replace any occurence of Target.Value with Range("B102").Value.

If you wish to run it as an event procedure, you would change the macro name to<PRE>Private Sub Worksheet_Change(ByVal Target As Range)</PRE>which would cause it to be an event procedure if placed in the code section for the worksheet.

Hope this clears it up a bit,

K
This message was edited by kkknie on 2002-07-30 09:04

 

[mlopes1]

I have a similar question except its for rows. How would I modify the previously posted code to hide a row if cell A of that row was empty. Cell A:row references another worksheet but sometimes there is nothing in that referenced cell. I would like the entire row hidden if this is true. Any ideas? Thanks

 

[kkknie]

You would use the same general format. The key would be how to activate the code. If you just want to attach it to a button there should be no problems. If you want it to run whenever the other sheet updates, I'm clueless... The Worksheet_Change event doesn't seem to fire when the value in the other sheet changes.

Here's some code to attach to a button (or anywhere you want to run the code):

<PRE>For Each r In Range("A3:A100")

If r.Value = 0 Then
r.EntireRow.Hidden = True
Else
r.EntireRow.Hidden = False
End If

Next</PRE>This example assumes you only want to check cells A3 to A100 and want to hide it if the value is 0. You should change the range and the value to check to suit your needs.

K

 

[RazzUK]

Hello!

I found this thread via google so I apologise for the "bump"

I'm trying to create a macro to hide rows for a Risk Assessment spreadsheet. This thread has been helpful far, however I've hit a slight (hopefully) snag. When I click the checkboxes the rows I want to be hidden are hidden without fault, however, the checkboxes remain. Is there a code to hide checkboxes once hidden? In addition to this I'm using a Commandbox to recall the the hidden rows. Please find my current code below.

------------------------
'Below are the checkboxes
Sub CheckBox13_Click()

ScreenUpdating = True

For Each r In Range("D15:D18")

If r.Value = 0 Then

r.EntireRow.Hidden = True

ElseIf r.Value = 1 Then

r.EntireRow.Unhidden = True

End If


Next

End Sub

Sub CheckBox17_Click()
For Each r In Range("D19:D22")

If r.Value = 0 Then

r.EntireRow.Hidden = True

ElseIf r.Value = 1 Then

r.EntireRow.Unhidden = True

End If


Next
End Sub

'Below is the button I use to unhide all the hidden rows
Sub Button15_Click()

For Each r In Range("D1:D122")


If r.Value = 0 Then

r.EntireRow.Hidden = False

End If

Next

End Sub
--------------------

Please help. In a nutshell I need to be able to hide the checkboxes once they've been clicked and then unhide them, along with the rows, when I click the command button.

Many thanks in advance!

-Razz

 

[brent.fraser]

Hi,

I came across your your post and I am trying that in a spreadsheet and it is sort-of working.

I am hiding and showing columns based on the value in cell $D$13. If the cell is "Meets Expectations" then the colunm is hidden. If the value is "Gap Identified" then the column is not hidded.

This is the VB code I am using:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$D$13" And Target.Text = "Meets Expectations" Then
Range("I:I").EntireColumn.Hidden = True
Else
If Target.Address = "$D$13" And Target.Value = "Gap Detected" Then
Range("I:I").EntireColumn.Hidden = False
End If
End If
End Sub

The thing is that $D$13 is a formula that is as follows:

='1-Skill Development Assessment '!J25

So I have two options, the first is to amend the VBA code (I think it has something to do with "Target.Value") or 2, to reference the page and cell number it is refeencing.

Any thoughts?

Thanks all.

 

[Blaise]

Hello All,

I'm brand new to this forum and I'm also brand new to visual basic.

As I'M not an expert of this field I've been creating an Excel for weeks for calculating commission for an investment. I've run through several threads to find out what code I could use in VB for auto hide/expand rows if one cell meets some requirements but I couldn't solve the problem. I've managed to solve the issue but when I protect the sheet (as I don't want anyone to change the formulas), it gives an error message.

I would appreciate if someone could solve this problem. I've uploaded the file here: www.biosyourlife.eu/download/Botanoo_kalkulacio_v8_ENG.zip

The sheet name is 'Investor'. It is not protected at the moment, so everything works fine. The auto hide or expansion only depends on the value given in cell E2. The first expansion (36-45) occurs when the value of E2 is 9, the second (rows 47-50) occurs when it is 17.

It would be great if someone could send the corrected file back to me to the gyorivanyi.balazs@gmail.com address. Please use the '123' as password.

Thank you very much in advance.

All the best,
Balazs