Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to tell if a cell is hidden by 'Autofilter'
? cells(18,4).EntireRow.Hidden
False Rows(18).Hidden = True ? cells(18,4).EntireRow.Hidden True -- Regards, Tom Ogilvy TonyJeffs wrote in message om... Suppose I use the autofilter command so that only rows 10 16 18 22 are visible, Is it possible to establish whether a cell is visible using a macro? Something like: msgbox cells(18,4).visible Thanks Tony |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to tell if a cell is hidden by 'Autofilter'
you might want to think a bit differently
dim rng as Range dim rng1 as Range set rng = Activesheet.Autofilter.Range.Columns(1).cells ' adjust range not to include header row set rng = rng.offset(1,0).Resize(rng.rows.count-1) ' adjust range to refer only to visible cells On Error Resume Next set rng1 = rng.SpecialCells(xlVisible) On Error goto 0 if not rng1 is nothing then for each cell in rng1 sStr = sStr & cell.Address & ", " Next sStr = left(sStr,len(sStr)-2) msgbox sStr Else msgbox "No visible rows" End if This will allow you to work with the visible cells in the filter Tom Ogilvy wrote in message ... ? cells(18,4).EntireRow.Hidden False Rows(18).Hidden = True ? cells(18,4).EntireRow.Hidden True -- Regards, Tom Ogilvy TonyJeffs wrote in message om... Suppose I use the autofilter command so that only rows 10 16 18 22 are visible, Is it possible to establish whether a cell is visible using a macro? Something like: msgbox cells(18,4).visible Thanks Tony |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to tell if a cell is hidden by 'Autofilter'
Just something for discussion...When you use the term "Hidden", and
"RowHeight," just be aware that Excel considers a Row with a height of about 0.6 or less as "Hidden." Fortunately, a height of about 0.6 is rather narrow, but Excel does considered it "hidden." You can test some ideas with your display with this: Sub Demo() Dim H For H = 0 To 1 Step 0.1 With [A10].EntireRow .RowHeight = H Debug.Print _ Format(H, "0.0 - "); Format(.RowHeight, "0.00 "); .Hidden End With Next H End Sub Here are my results: 0.0 - 0.00 True 0.1 - 0.25 True 0.2 - 0.25 True 0.3 - 0.50 True 0.4 - 0.50 True 0.5 - 0.50 True 0.6 - 0.50 True 0.7 - 0.75 False 0.8 - 0.75 False 0.9 - 1.00 False 1.0 - 1.00 False My RowHeight gets rounded to the nearest 0.25 value. If you want, you can adjust Tom's excellent idea with the following... If Not rng1 Is Nothing Then sStr = rng1.Address(False, False) Debug.Print sStr Else MsgBox "No visible rows" End If Prints the string: A10,A16,A18,A22 -- Dana DeLouis Using Windows XP & Office XP = = = = = = = = = = = = = = = = = "TonyJeffs" wrote in message om... Thanks for the explanations. I understand it now. Hopefully one day, this'll all become intuitive to me! Tony |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP: Selecting Non-Visible (Hidden) Cells with AutoFilter Applied | Excel Discussion (Misc queries) | |||
HELP: Selecting Non-Visible (Hidden) Cells when AutoFilter is Appl | Excel Worksheet Functions | |||
HELP: Selecting Non-Visible (Hidden) Cells with AutoFilter Applied | Excel Discussion (Misc queries) | |||
HELP: Selecting Non-Visible (Hidden) Cells with AutoFilter Applied | Excel Discussion (Misc queries) | |||
Is it possible to tell if a cell is hidden by 'Autofilter' | Excel Programming |