Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide and Unhide rows automaticely
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enabling option „Format rows“ to hide/unhide rows using VBA-code? | Excel Discussion (Misc queries) | |||
How to Hide and Unhide Rows | Excel Discussion (Misc queries) | |||
CommandButton used to hide and unhide rows | Excel Discussion (Misc queries) | |||
Hide Unhide Rows | Excel Discussion (Misc queries) | |||
How to hide and unhide rows | Excel Programming |