Hiding cells based on formula result

[Jim]

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

 

[Tom Ogilvy]

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

 

[Jim Ashley]

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!

 

[Tom Ogilvy]

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

 

[Jim Ashley]

That worked thank you!!!



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!