How do you hide a row based upon cell criteria?

[Jim Ashley]

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!

 

[Kurt]

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

 

[Kurt]

should read a3 - a10 ...

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 May]

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,,

 

[Jim May]

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,

 

[Kurt]

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