Hiding cells based on formula result
 

I am working with dates based upon month and year entry.
My formula returns day between 1 and 31, unless no day
exists then "" is used.
I want to hide the row, if the cell value is "". How do I
do that?
Thanks, Jim

Hiding cells based on formula result
 

Sub HideRow()
Dim rng As Range, LastRow As Long
Dim i As Long
Set rng = ActiveSheet.UsedRange.Columns(1).Cells
LastRow = rng(rng.Count).Row
For i = LastRow To 1 Step -1
if cells(i,1).Value = "" then
Cells(i,1).Entirerow.Hidden = True
else
Cells(i,1).Entirerow.Hidden = False
end if
Next
End Sub

--
Regards,
Tom Ogilvy


"Jim" wrote in message
...
I am working with dates based upon month and year entry.
My formula returns day between 1 and 31, unless no day
exists then "" is used.
I want to hide the row, if the cell value is "". How do I
do that?
Thanks, Jim

Hiding cells based on formula result
 

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!

Hiding cells based on formula result
 

you only want to check cells A40, A41 and A43?

right click in the sheet tab and select view code.

Paste in code like this:

Private Sub Worksheet_Calculate()
Dim varr As Variant
Dim i As Long
varr = Array(0, 1, 3)
For i = LBound(varr) To UBound(varr)
If Range("A40").Offset(varr(i), 0).Value = "" Then
Range("A40").Offset(varr(i), 0). _
EntireRow.Hidden = True
Else
Range("A40").Offset(varr(i), 0). _
EntireRow.Hidden = False
End If
Next
End Sub

--
Regards,
Tom Ogilvy

"Jim Ashley" wrote in message
...
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!