Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you hide a row based upon cell criteria?
I am trying to get a spreadsheet to hide the rows that do not have a day
number in the left most cell. If I put the formula in the other cells, it will be overwritten when data is put into them for months that have more than 28 days. Specifically, The date is entered in cell A7. The formula in A40 is "=IF(DAY($A$7+28)<DAY(A39),"",$A$7+28)". The formula in A41 is "=IF(DAY($A$7+29)<DAY(A39),"",$A$7+29)". The formula in A43 is "=IF(DAY($A$7+30)<DAY(A39),"",$A$7+30)". If the result or the formula enters "" in the cell, I want to hide that row. I would like the macro to run each time the date in A7 is changed. I am a novice with visual basic. Is there a reference that I should have to get started understanding this language? Thanks again, Jim *** Sent via Devdex http://www.devdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you hide a row based upon cell criteria?
Yes, sorry. I had column B with the simple formula "=A3", etc so that a
change to one of the rows in column "A" caused a re-calculation event. I'm glad you found your way through my omission. ....kurt "Jim May" wrote in message news:iafBc.4058$HN5.192@lakeread06... OK, Just entering a value in a cell doesn't trigger Calculate. When I entered a computational entry =b1*c1 in d1 it worked; Thanks, "Jim May" wrote in message news:GjeBc.4053$HN5.778@lakeread06... Kurt: I can't seem to get your illustration/example going. My "limited" understanding of event-code that is Worksheet_Calculate is as follows: I have AutoCalculate "on" - so on a ws like this example if I enter say the value 1 in cell b1 and return the Worksheet Calculates, Right? << which should run your Worksheet_Calculate procedure, Right? I'm having to Alt-F11 and in sheet1 "Run the Worksheet_Calculate Code" which hides all my even-numbered rows. Also, I'm having to manually unhide them - Selecting rows 2 - 11 and then Format, Row, Unhide... I'm missing several things here. Can you assist / clarify? TIA,, "Kurt" wrote in message ... should read a3 - a10 ... "Kurt" wrote in message ... Use the Worksheet_Calculate in conjunction with a procedure to hide a row, and one to show (unhide) a row. Here is an example: In this example, the cells a3 = a10 are filled with the values 0 - 7. The main procedure (which runs whenever the worksheet is re-calculated) checks the values in the first column and if it is an even value, hides the row. There is also a procedure to show the row if the value changes, but of course in this simple example you can't change it 'cause it's hidden! But a formula that changes its value in a re-calc will unhide it. Private Sub Worksheet_Calculate() Dim i As Integer For i = 3 To 10 If ((Cells(i, 1).Value) Mod 2) = 0 Then hide_row (i) Else show_row (i) End If Next i End Sub Sub hide_row(ByVal i As Integer) Dim str As String str = CStr(i) + ":" + CStr(i) Rows(str).Select Selection.EntireRow.Hidden = True End Sub Sub show_row(ByVal i As Integer) Dim str As String str = CStr(i) + ":" + CStr(i) Rows(str).Select Selection.EntireRow.Hidden = False End Sub "Jim Ashley" wrote in message ... I am trying to get a spreadsheet to hide the rows that do not have a day number in the left most cell. If I put the formula in the other cells, it will be overwritten when data is put into them for months that have more than 28 days. Specifically, The date is entered in cell A7. The formula in A40 is "=IF(DAY($A$7+28)<DAY(A39),"",$A$7+28)". The formula in A41 is "=IF(DAY($A$7+29)<DAY(A39),"",$A$7+29)". The formula in A43 is "=IF(DAY($A$7+30)<DAY(A39),"",$A$7+30)". If the result or the formula enters "" in the cell, I want to hide that row. I would like the macro to run each time the date in A7 is changed. I am a novice with visual basic. Is there a reference that I should have to get started understanding this language? Thanks again, Jim *** Sent via Devdex http://www.devdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hide row based on cell value | Excel Worksheet Functions | |||
Hide row based on cell value | Excel Worksheet Functions | |||
Macro to hide and unhide based on criteria | Excel Discussion (Misc queries) | |||
Is there a way to HIDE a row based on a value of a cell ? | Excel Discussion (Misc queries) | |||
Hide Row Based on cell value | Excel Programming |