#1
|
|||
|
|||
Hide Rows and Update Chart based on cell value
Hi,
I have input cell - Retirement Age. I was wondering if it is possible for me to hide the rows based on the input in this cell. For example: if the value in retirement age cell is 55 then it should hide all filled rows after that row. At the same time I have graph, it should update the graph (I mean chart) to upto age 55 only. |
#2
|
|||
|
|||
Yes, this isn't too hard.
A good way to code up something like this is to start by recording a macro where you hide rows and update the chart data and then modify the resulting code to meet your purpose. Looking a recorded code is usually the easiest way to work out how to reference the objects that you are interested in and to identify the properties/methods of these objects that you should be calling to achieve your desired outcome. Between the code that you record and the sample code below hopefully you will be able to craft a suitable solution! Something like this would be able to hide the rows based upon the retirement age... Code:
Public Sub hideRows()'Declare variables Dim i As Integer 'For iterating through rows Dim totalRows As Integer 'Stores row number of bottom row on worksheet Dim retireAge As Integer 'Stores retirement age 'Initialise variables retireAge = Sheets("Sheet1").Range("B3").Value totalRows = Sheets("Sheet1").UsedRange.Rows.Count 'Set to index of last row in UsedRange totalRows = Sheets("Sheet1").UsedRange.Rows(totalRows).Row 'Get row number of last row in UsedRange 'Loop through rows For i = 1 To totalRowsEnd Sub'If any rows below retirement age are hidden, then unhide them If i <= retireAge And Sheets("Sheet1").Rows(i).EntireRow.Hidden = True ThenNext iSheets("Sheet1").Rows(i).EntireRow.Hidden = False'If any rows above retirement age are shown, then hide them ElseIf i > retireAge And Sheets("Sheet1").Rows(i).EntireRow.Hidden = False ThenSheets("Sheet1").Rows(i).EntireRow.Hidden = TrueEnd If Code:
Public Sub updateGraph()'Declare variables Dim retireAge As String 'Stores retirement age 'Initialise variables retireAge = Sheets("Sheet1").Range("B3").Value 'Set source range for chart 'If there is only one chart on Sheet1 and this is the chart we want to update then... Sheets("Sheet1").ChartObjects(1).Chart.SetSourceData Source:=Sheets("Sheet1").Range("A3:A" + retireAge) 'If chart has been added as its own tab in the workbook then... Charts("Chart1").SetSourceData Source:=Sheets("Sheet1").Range("A3:A" + retireAge)End Sub Code:
Private Sub Worksheet_Change(ByVal Target As Range)'This subroutine is called EVERY TIME a change is made to the worksheet, so it is important to ensure that 'our macros are only trigger when the retirement age is changed If Target.Address = "$B$3" ThenEnd SubCall hideRows Call updateGraphEnd If |
#3
|
|||
|
|||
Hi,
I have entered the code mentioned in function Public Sub hideRows(). It does not through any error and does not seem to do anything. Any idea why? |
#4
|
|||
|
|||
You will need to modify the code for your spreadsheet. In particular the line
retireAge = Sheets("Sheet1").Range("B3").Value needs to be changed to point to the cell containing the retirement age and all other Sheets("Sheet1")'s should refer to the worksheet on which the rows are to be hidden. |
#5
|
|||
|
|||
indeed, i have made the changes as required thats why its not throwing any error. however it at the same time it does not perform any function.
|
#6
|
|||
|
|||
If it isn't throwing any errors then it should be doing something.
Try manually unhiding all rows on your worksheet and then re-running the macro to see if it does anything. If it still doesn't work, would it be possible to attach your spreadsheet? |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Macro based on cell value | ubns | Excel Programming | 1 | 05-07-2012 04:03 AM |
Calculating a cell value based on many variables | malawimick | Excel | 0 | 12-14-2011 05:29 PM |
How to highlight the row based on cell value? | Learner7 | Excel | 1 | 12-11-2011 02:28 AM |
Temporarily show/hide selected table rows | glricht | Word Tables | 0 | 12-29-2009 05:40 AM |
Conditional Formatting to Hide Rows or Columns? | sczegus | Excel | 0 | 09-26-2006 04:17 PM |