Run macro on cell text change

[TomDuff]

Basically I have a macro which checks whether a cell is empty and if it is, it hides the row the cell is in. I want this to run every time the value of the cell (which contains a formula linked to another sheet) changes, how would I do this? I am a huge noob and have basically no knowledge of VBA at all...

Here is my macro:

[VBA]
Sub HideEmptyRowsB34()
Dim cell As Range
Rows("34").EntireRow.Hidden = False
For Each cell In ActiveSheet.Range("B34")
cell.EntireRow.Hidden = Len(cell.Text) = 0
Next cell
End Sub
[/VBA]
Any help would be greatly appreciated!

Tom

[Bob Phillips]

[vba]

Private Sub Worksheet_Calculate()
Dim cell As Range
With Me.Range("B34")
.EntireRow.Hidden = Len(.Text) = 0
End With
End Sub
[/vba]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[TomDuff]

Works like a charm, thanks mate!

[TomDuff]

I'm trying to do this for multiple cells and their associated rows but it's very slow if it has to check every row and rehide them every time a change is made to one of the cells. I tried making each calculation only affect the row of the cell which has changed using this:

[VBA]Private Sub Worksheet_Calculate()
Dim cell As Range
Rows("34").EntireRow.Hidden = False
With Me.Range("B34")
.EntireRow.Hidden = Len(.Text) = 0
End With
End Sub
Private Sub Worksheet_Calculate()
Dim cell As Range
Rows("5").EntireRow.Hidden = False
With Me.Range("B35")
.EntireRow.Hidden = Len(.Text) = 0
End With
End Sub
[/VBA]
but I get this message when I alter one of the cells:

"Compile Error:

Ambiguous name detected"

Does anyone know a way around this?

[Bob Phillips]

You cannot have multiple Calculate subs, and it will check every time the worksheet recalculates, cannot be avoided I am afraid.

If the formula in B34/B35 is not that complex, and the precedent cells are static you could always check those cells on a change event.