Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-02-2012, 10:52 PM
ubns ubns is offline Hide Rows and Update Chart based on cell value Windows 7 32bit Hide Rows and Update Chart based on cell value Office 2010 32bit
Competent Performer
Hide Rows and Update Chart based on cell value
 
Join Date: Apr 2012
Posts: 177
ubns is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 05-04-2012, 07:16 AM
Peter Wienand Peter Wienand is offline Hide Rows and Update Chart based on cell value Windows 7 64bit Hide Rows and Update Chart based on cell value Office 2010 32bit
Novice
 
Join Date: May 2012
Posts: 15
Peter Wienand is on a distinguished road
Default

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 totalRows
'If any rows below retirement age are hidden, then unhide them If i <= retireAge And Sheets("Sheet1").Rows(i).EntireRow.Hidden = True Then
Sheets("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 Then
Sheets("Sheet1").Rows(i).EntireRow.Hidden = True
End If
Next i
End Sub
The code for updating the chart will depend on what type of chart you are using.

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
If you want to get fancy, you could make the updates automatically apply whenever the retirement age is updated using the relevant worksheet's change event... (note that this code, as per the examples above, assumes that the retirement age is located in cell B3 of the worksheet)

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" Then
Call hideRows Call updateGraph
End If
End Sub
Reply With Quote
  #3  
Old 05-07-2012, 04:38 AM
ubns ubns is offline Hide Rows and Update Chart based on cell value Windows 7 32bit Hide Rows and Update Chart based on cell value Office 2010 32bit
Competent Performer
Hide Rows and Update Chart based on cell value
 
Join Date: Apr 2012
Posts: 177
ubns is on a distinguished road
Default

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?
Reply With Quote
  #4  
Old 05-07-2012, 05:27 AM
Peter Wienand Peter Wienand is offline Hide Rows and Update Chart based on cell value Windows 7 64bit Hide Rows and Update Chart based on cell value Office 2010 32bit
Novice
 
Join Date: May 2012
Posts: 15
Peter Wienand is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 05-07-2012, 05:38 AM
ubns ubns is offline Hide Rows and Update Chart based on cell value Windows 7 32bit Hide Rows and Update Chart based on cell value Office 2010 32bit
Competent Performer
Hide Rows and Update Chart based on cell value
 
Join Date: Apr 2012
Posts: 177
ubns is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 05-07-2012, 05:44 AM
Peter Wienand Peter Wienand is offline Hide Rows and Update Chart based on cell value Windows 7 64bit Hide Rows and Update Chart based on cell value Office 2010 32bit
Novice
 
Join Date: May 2012
Posts: 15
Peter Wienand is on a distinguished road
Default

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?
Reply With Quote
Reply

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
Hide Rows and Update Chart based on cell value 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:11 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft