Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding cells based on the value in another cell | Excel Discussion (Misc queries) | |||
Scan group of cells and result based on condition | Excel Discussion (Misc queries) | |||
lock cells based on formula result | Excel Discussion (Misc queries) | |||
Change font and background color of several cells based on result of a formula | Excel Discussion (Misc queries) | |||
conditional formatting based on another cells formula result | Excel Discussion (Misc queries) |