 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
hi
I cant seem to work out a way of being able to hide /un-hide specific rows from a dataset which appear in a series of graphs.
the data which appears in a graph has been selected from a number of columns. it would be nice if i can selectively remove rows of data by clicking a 'hide' button by the side of each row of data and later be able to 'un-hide' the data and therefore allowing it to reappear in the graph.
any suggestions for any macro code?
cheers
pete
Excel can be quite fussy about that - graphs only display data from non-hidden cells, so the minute you hide the row / column, that data goes from your graph.
You can hide rows normally.. does that not exclude them from the graph?
As for code - detect double click on a cell using
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Then get the row / column using
Dim row As Integer
Dim col As Integer
row = Target.row
col = Target.Column
Then hide / unhide using
activesheet.rows(row).hidden = true / false
so you can hide by double clicking... won't work for unhide though as you can't double click in a hidden row....
Maybe add a RESET button...
I don't mind if the hidden data doesn't show on the graph as that's the point of what I'm after.. all i care about is that it can be easily added back to the graph.
I'm really new to this coding game but so i'll try and give what you have suggested a go, cheers
Forget macros...
Have the data you want to be shown in the graph picked by using drop down lists and vlookups to these.
Include as many drop downs as you want with a blank option for removing certain data lines.
If you're new to Excel VBA - this site is really useful:
http://www.cpearson.com/Excel/MainPage.aspx
Ben
Excel charts do not display data in hidden rows or columns.
You could add a group of ActiveX check boxes to the spreadsheet, one for each data series,
e.g.
Product_A, Product B etc
then add a bit of code to each check box's 'Click' event that hides or unhides the data series it relates to depending on it's present state:
e.g for a data series named "Product_A"
Private Sub CheckBox1_Click()
ActiveSheet.Range("Product_A").EntireRow.hidden = _ (or EntireColumn)
Not ActiveSheet.OLEObjects1().Object.Value
end sub