How to auto hide a row if the first cell is blank?

[TG]

Ok I have checked for postings on this problem but none of them have an
answer to my "unique" question.

What I want to do is hide an entire row if the first cell is blank but the
"blank" cell has a formula.
For example:
Let’s say cell A25 has the following formula if(A24="33","Please input
data"," "), so if A24 does not have the number 33 or if it is blank then the
cell A25 will be blank as well, therefore I want the whole entire row to
hide. If A24 has the number 33 then the row should appear with the cell A25
showing "please input data".

I have found similar problems that have solutions that work but only if the
cell is completely blank and with no "hidden" formulas.
(I am by no means well off with VBA or programming, but ill try it if that
is the only way to do so.)

I hope I make some sense


Thank you in advance.
TG

 

[Gord Dibben]

First, change your formula in A25 to

=IF(A24=33,"Please Input Data","")

note: no space in last argument and quotes not needed for numeric entries.

Then copy/paste this into your sheet module.

Private Sub Worksheet_Calculate()
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("A25")
If .Value = "" Then
.EntireRow.Hidden = True
Else
.EntireRow.Hidden = False
End If
End With
stoppit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[TG]

Thank you very Much Gord, this is exactly what i was looking for for this
paticular situation. However, I have another row that i would like to do the
same to but it has a drop down menu on a cell on that row. Can I do the same
while also hiding that particular drowdown menu?

Any Ideas?

 

[Gord Dibben]

Always best to state which "other row" and what does the dropdown menu have
to do with it?

What criterion would be used to hide the row and in which cell is that
criterion?

If you hide the row based a value you would have no access to the menu. Is
that what you want?


Gord

 

[Gord Dibben]

Here is some revised code which works on two rows with formulas in A14 and
A25 which return a value or ""

Private Sub Worksheet_Calculate()
Const MyCells As String = "A14,A25" 'adjust the range to suit
On Error GoTo stoppit
Application.EnableEvents = False
For Each cell In Me.Range(MyCells)
With cell
If .Value = "" Then
.EntireRow.Hidden = True
Else
.EntireRow.Hidden = False
End If
End With
Next
stoppit:
Application.EnableEvents = True
End Sub


Gord