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 :
