ExcelBanter - Hide and Unhide rows automaticely

Automatically: no, not with any standard excel option.

With macro's: yes, but An change_event macro would be totally useless:
if a user start typing in a row it is hidden unless he start typing in
j,k or l

you could call the macro from the sheet activate event.

note: the code is ok until the union reaches around 600 areas...
then performance will degrade rapidly.

Sub HideUnHideJKL()
Dim rJKL As Range, rRow As Range, rHide As Range

With ActiveSheet
Set rJKL = Intersect(.UsedRange, .Range("J:L"))
End With

'populate rHide so no need to test the union
Set rHide = rJKL.Offset(rJKL.Rows.Count).Cells(1)
For Each rRow In rJKL.Rows
If Application.CountA(rRow) = 0 Then
Set rHide = Union(rHide, rRow)
End If
Next

Application.ScreenUpdating = False
With ActiveSheet
'strip the dummy from rHide
Set rHide = Intersect(.UsedRange, rHide)
.UsedRange.EntireRow.Hidden = False
rHide.EntireRow.Hidden = True
End With
Application.ScreenUpdating = True

End Sub

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

r v deursen wrote :

Can i hide the specific row (here row 10) when there is
no data in J10 - K10 - L10
and when there is data in one or more of these cells the
row unhides automaticely

Thank you