Hide row when cell is emtpy

[Hannes Heckner]

I want to define a row range in which a row is hidden when a certain cell in
this row is empty.

any suggestions?

Best regards
Hannes

 

[Mark Rosenkrantz]

Hannes;

Something like

If Isempty(ActiveCell) Then
ActiveCell.EntireRow.Hidden= True
End If

Mark.

 

[Bob Phillips]

Hannes,

Like this?

For i = 1 to 100
If cells(i,"H").Value = "" then
cels(i,"H").entirerow.visible = false
End if
Next i

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Hannes Heckner]

Hi,

thank for your help, but I want this behaviour to apply permanently to a
certain range. So whenever I put data in this range the macro should be
executed immediately.

Probably I will need something like events but this is not a feature of excel i
learned, is it?

Best regards
Hannes

 

[Bob Phillips]

Hi Hannes,

Yes, you are talking event code. This code will make a row hidden if the
cell in column H is empted. Note that crucial qualification, if it is
emptied, it will not affect any rows where H is already empty.

As worksheet code, it goes in the worksheet event code module. Right-click
on the sheet name tab, select View Code, and paste this code in

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 8 Then
If Target.Value = "" Then
Target.EntireRow.Hidden = True
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)